What Most People Miss About How SUBTOTAL Formula Works in Excel

It’s 4:47 PM on Friday. Your manager just asked for a consolidated report by 5. You have 12 spreadsheets open and no idea how to combine them. You paste everything into one sheet, apply AutoFilter to show only Q3 sales, and type =SUM(C2:C100). You hit Enter. The number looks right—until you realize it’s summing all rows, including the ones you just filtered out. You panic. Then you remember someone mentioned SUBTOTAL. But what does that 9 or 109 actually mean? And why did your colleague’s version return zero?

The Problem

You’re tracking regional sales across 8 territories. Data is in columns A:E — Territory, Rep Name, Date, Units Sold, Revenue. You’ve applied AutoFilter to show only Q3 entries (July–September 2024), but your =SUM(E2:E100) still includes rows for April and May. Worse: you added manual row hiding to hide test entries (rows 12, 27, and 41), and now SUM counts those too. Your dashboard shows $312,890 — but the filtered view only displays $187,240 worth of visible rows.

Territory	Rep Name	Date	Units Sold	Revenue
Northwest	Sarah Chen	2024-07-12	42	$14,360
Southeast	James Lopez	2024-08-03	31	$10,570
Midwest	Amina Patel	2024-07-29	56	$19,150
Northeast	David Kim	2024-09-05	28	$9,580
Southwest	Lena Torres	2024-08-17	49	$16,760
Northwest	Sarah Chen	2024-06-14	33	$11,290
Midwest	Amina Patel	2024-09-22	61	$20,860
Northeast	David Kim	2024-05-30	22	$7,520

That last row? It’s May — not Q3 — and shouldn’t be included. But SUM(E2:E9) doesn’t care. It adds every cell in the range. And if you manually hid row 6 (the June entry), SUM still includes it. That’s the core frustration: Excel’s basic functions ignore visibility state entirely.

The Solution

SUBTOTAL fixes this — but only if you use the right function number and understand its dual behavior. It’s not magic. It’s arithmetic with context awareness.

Type =SUBTOTAL(9,E2:E9) in cell E11. The 9 tells Excel to run SUM, but with one critical rule: skip hidden rows (both filtered and manually hidden).
Apply AutoFilter to column C (Date). Click the dropdown → Custom Filter → “is after or equal to” 2024-07-01 and “is before or equal to” 2024-09-30. Rows for May and June disappear visually.
Check E11. It now shows $81,270 — the sum of only the 5 visible Q3 rows. Not $93,090 (the full SUM).
Now manually hide row 7 (Amina Patel’s July 29 entry). Press Ctrl+9. Watch E11 drop to $62,120. SUBTOTAL(9,…) respects both filter and manual hide.

What You Did	Formula Used	Result	Shortcut
Initial full-range sum	`=SUM(E2:E9)`	$93,090	—
After Q3 filter applied	`=SUBTOTAL(9,E2:E9)`	$81,270	Alt+= (then edit)
After hiding row 7	`=SUBTOTAL(9,E2:E9)`	$62,120	Ctrl+9
Same filter + `SUBTOTAL(109,E2:E9)`	`=SUBTOTAL(109,E2:E9)`	$81,270	Alt+M, U, S (for Subtotal dialog)

Here’s the counterintuitive part: 9 and 109 both do SUM — but 109 ignores only filtered rows, not manually hidden ones. So if you hide row 7 manually, SUBTOTAL(109,…) still includes it. Use 9 if you need full visibility-awareness. Use 109 only when you want to ignore filters but keep manually hidden rows in the math.

Going Further

You can nest SUBTOTAL inside other formulas. Try this in F2: =IF(SUBTOTAL(103,A2:A9)>1,"Multiple reps","Single rep"). 103 counts visible non-blank cells in column A — perfect for dynamic headers. Or combine with AVERAGE: =SUBTOTAL(1,E2:E9) (1 = AVERAGE) gives you the average of visible rows only.

Need running totals that respect filters? Put =SUBTOTAL(9,$E$2:E2) in F2 and drag down. Each row sums only visible rows from the top down to itself. Yes — it works even with filters active.

One more trick: If you use Excel Tables (Ctrl+T), SUBTOTAL automatically appears in the Total Row. Right-click any cell in the table → “Table → Total Row”. Click the dropdown in the total cell and pick “Sum”. Excel inserts SUBTOTAL(109,…) — because Tables don’t support manual row hiding, so 109 is safer.

When NOT to Use This

Don’t use SUBTOTAL if you’re aggregating data across multiple worksheets. SUBTOTAL won’t work in 3D references like SUBTOTAL(9,Sheet1:Sheet3!E2:E9) — Excel returns #VALUE!. Use SUM or SUMIFS instead.

Avoid SUBTOTAL inside array formulas (pre-Excel 365). It breaks. In older versions, =SUBTOTAL(9,IF(A2:A9="Northwest",E2:E9)) returns an error. Use SUMPRODUCT or SUMIFS for conditional visible-only sums.

And never use SUBTOTAL(9,…) on a range that includes another SUBTOTAL result. It double-counts. If row 10 already has =SUBTOTAL(9,E2:E9), don’t then write =SUBTOTAL(9,E2:E10). Excel treats nested SUBTOTALs as regular numbers — no recursion protection.

Keyboard Shortcuts

Action	Shortcut	Notes
Insert AutoSum (then edit to SUBTOTAL)	`Alt+=`	Press again to cycle through SUM, AVERAGE, COUNT — then type “SUBTOTAL”
Hide selected rows	`Ctrl+9`	Critical for testing SUBTOTAL(9) vs (109)
Open Subtotal dialog (for grouped data)	`Alt+M, U, S`	Only works on sorted, grouped ranges — not for simple visibility-aware sums
Toggle AutoFilter	`Ctrl+Shift+L`	Fast way to test visible-row behavior