Stop Using SUM — This Is How SUBTOTAL Function Works in Excel

The first thing most people do when they need a total after filtering is type =SUM(C2:C100). That’s the mistake — it adds up everything, including rows Excel hid. You’ll think your Q2 sales total is $382,450. It’s actually $291,170. And no one notices until finance reconciles.

The Problem

You’ve got a sales report with 97 rows. You filter by Region = "Asia" and want to see total revenue. You highlight C2:C97, hit AutoSum, and get $412,680. But that number includes rows for EMEA and Americas — still in the range, just invisible. Worse: if someone collapses an outline or hides rows manually, SUM doesn’t care. Neither does COUNT or AVERAGE. Your dashboard shows garbage. Finance asks where the $91K gap came from. You check the filter again. Nothing looks wrong.

Sales Rep	Region	Revenue	Date
Sarah Chen	Asia	$42,500	2024-03-15
Diego Mora	EMEA	$38,200	2024-03-16
Amina Patel	Asia	$51,900	2024-03-17
James Wu	Americas	$29,400	2024-03-18
Linh Tran	Asia	$63,100	2024-03-19
Rafael Costa	EMEA	$34,700	2024-03-20
Yuki Sato	Asia	$47,300	2024-03-21
Miguel Reyes	Americas	$22,800	2024-03-22

That table shows rows 2–9. If you filter Region = Asia, rows 4, 6, and 8 disappear — but =SUM(C2:C9) still returns $311,800. The real visible sum? $204,800. Off by over $107K.

The Solution

Select the cell where you want the total (e.g., C11).
Type =SUBTOTAL(9,C2:C9). The 9 means “use SUM”, and the range is your data column.
Press Enter. You’ll see $204,800 — correct for visible rows only.
Now try filtering Region = Asia. Watch the total update instantly to $204,800. Try hiding row 5 manually (right-click → Hide). The total stays $204,800 — it ignored the hidden row.

This works because SUBTOTAL knows the difference between filtered-out rows and deleted rows. It treats manual row hiding the same as filter exclusion — unlike SUM, which sees every cell in the range, period.

Sales Rep	Region	Revenue	Date
Sarah Chen	Asia	$42,500	2024-03-15
Amina Patel	Asia	$51,900	2024-03-17
Linh Tran	Asia	$63,100	2024-03-19
Yuki Sato	Asia	$47,300	2024-03-21
Total (SUBTOTAL)		$204,800

Note: The formula in C11 is =SUBTOTAL(9,C2:C9), not =SUBTOTAL(109,C2:C9). We’ll explain that difference in the next section.

Going Further

SUBTOTAL has two sets of function numbers: 1–11 and 101–111. Use 1–11 if you want to ignore both filtered-out and manually hidden rows. Use 101–111 if you only want to ignore filtered rows — but include manually hidden ones. Yes, it’s backwards. Yes, everyone gets it wrong at first.

So =SUBTOTAL(9,C2:C9) sums visible rows only. =SUBTOTAL(109,C2:C9) sums visible rows plus any rows you hid with right-click → Hide. That’s rarely what you want — unless you’re doing a very specific audit workflow.

Here’s what else SUBTOTAL handles:

SUBTOTAL(1,C2:C9) = AVERAGE of visible cells only
SUBTOTAL(2,C2:C9) = COUNT of numbers in visible cells
SUBTOTAL(3,C2:C9) = COUNTA of non-blank visible cells
SUBTOTAL(4,C2:C9) = MAX of visible values
SUBTOTAL(5,C2:C9) = MIN

And here’s the counterintuitive tip: SUBTOTAL automatically ignores other SUBTOTAL results in the same range. So if you have subtotals in C10, C20, and C30, and you run =SUBTOTAL(9,C2:C40), it won’t double-count those three subtotals. Excel knows better than you do.

When NOT to Use This

SUBTOTAL fails silently in three cases:

Entire columns: =SUBTOTAL(9,C:C) looks clean, but it scans 1,048,576 rows — even blanks. It’ll work, but it’ll be slow. Always narrow your range: C2:C1000.
Non-contiguous ranges: =SUBTOTAL(9,C2:C10,E2:E10) returns #VALUE!. SUBTOTAL only accepts one continuous range.
Text-based criteria: You can’t use =SUBTOTAL(9,IF(B2:B10="Asia",C2:C10)) like you would with SUMPRODUCT. That array formula ignores SUBTOTAL’s filtering logic. Use FILTER + SUM in Excel 365 instead.

Also — don’t nest SUBTOTAL inside IFERROR expecting graceful fallbacks. =IFERROR(SUBTOTAL(9,C2:C10),0) will return 0 only if the range is invalid (e.g., deleted columns), not if all rows are filtered out. When nothing’s visible, SUBTOTAL returns 0 naturally — so the IFERROR is redundant and misleading.

Keyboard Shortcuts

Action	Shortcut	Notes
Insert SUBTOTAL via menu	`Alt + A + B`	Auto-creates grouped subtotals — use only if you need outline levels
Toggle filter	`Ctrl + Shift + L`	Essential pairing — filter first, then apply SUBTOTAL
Hide selected rows	`Ctrl + 9`	SUBTOTAL respects this — unlike SUM
Show hidden rows	`Ctrl + Shift + 9`	Watch your SUBTOTAL update live