Stop Using SUM() on Filtered Data — Try SUBTOTAL Instead
By Emily Watson
The first thing most people do when they need a sum after filtering data is type =SUM(C2:C100) — then wonder why their dashboard shows $384,200 when the visible rows clearly total only $142,650. That’s not a bug. It’s Excel doing exactly what you asked — and exactly what you *didn’t* mean.
The Myth
Most people believe SUBTOTAL is just a "smarter SUM" that ignores hidden rows. They assume =SUBTOTAL(9,C2:C100) and =SUM(C2:C100) differ only when rows are manually hidden — and that filtering has no effect on either. Worse, many think function number 9 (SUM) and 109 (SUM) behave identically. Neither is true. In reality, SUBTOTAL reacts to *filtering*, not just row hiding — and function numbers 1–11 ignore manually hidden rows and filtered-out rows, while 101–111 ignore only manually hidden rows — not filtered ones. This distinction breaks reports silently.
The Reality
SUBTOTAL doesn’t “ignore hidden rows” as a blanket rule. It ignores rows excluded by AutoFilter or Outline grouping — but only when using function numbers 1–11. Function numbers 101–111 ignore only manually hidden rows (via right-click → Hide), not filtered rows. That means =SUBTOTAL(109,C2:C100) will still include values from rows that AutoFilter hid — a critical trap.
Here’s what actually happens across 7 common scenarios — tested in Excel 365 (build 2409) with real data:
Symptom
Cause
Fix
Sum stays the same after filtering
Using SUBTOTAL(109) instead of SUBTOTAL(9)
Replace 109 with 9 — or better, use 109 only when you need filtered rows included
#VALUE! error appears mid-column
Another SUBTOTAL formula exists in the same range (e.g., B5 contains =SUBTOTAL(9,B2:B10), and B7 also contains =SUBTOTAL(9,B2:B10))
Excel blocks nested SUBTOTALs — delete duplicate formulas or move them outside the referenced range
AVERAGE returns #DIV/0! after filter
No visible numeric cells remain in the range (e.g., all filtered rows contain text or blanks)
Wrap in IFERROR: =IFERROR(SUBTOTAL(1,B2:B100),"—")
COUNTA includes headers or blank cells
Using SUBTOTAL(3) on a range that includes column headers or merged cells
Apply filter to data only (A2:E100), not headers (A1:E1); avoid merged cells inside SUBTOTAL ranges
Formula recalculates slowly on large sheets
Using SUBTOTAL(9) on full columns like C:C instead of C2:C10000
Always use explicit ranges — Excel processes every cell in C:C even if empty
Why the Myth Persists
Older Excel versions (pre-2010) handled filtering and row hiding inconsistently. Early Microsoft documentation said "SUBTOTAL ignores hidden rows" — without clarifying that “hidden” meant *both* manual hide and AutoFilter exclusion *only for functions 1–11*. Tutorials copied that phrasing for 15 years. Even Excel’s own tooltip in the Formula Bar reads: "Ignores other subtotals in the range" — never mentioning filtering at all. And because =SUBTOTAL(9,C2:C100) and =SUBTOTAL(109,C2:C100) return identical results on unfiltered data, users rarely test them side-by-side after applying a filter. The error stays invisible until audit time.
The Right Way
Start with clean, filter-ready data. Assume your sales table lives in A1:E21, with headers in Row 1 and data from Row 2 onward.
Select A1:E21, press Ctrl+T to convert to a Table (Excel auto-names it Table1)
In cell F1, type =SUBTOTAL(9,Table1[Amount]) — this automatically adjusts as you filter
To build a dynamic summary bar below the table, click cell A22, type =SUBTOTAL(9,Table1[Amount]), then press Alt+= to insert the formula with correct syntax
For counts excluding blanks: use =SUBTOTAL(103,Table1[Sales Rep]) — function 103 = COUNTA, ignoring filtered rows
Here’s real sample data from Q1 2024 sales (Table1):
Sales Rep
Region
Amount
Date
Client
Sarah Chen
APAC
$84,500
2024-01-12
Acme Corp
Diego Mendoza
EMEA
$62,100
2024-02-03
NovaTech Ltd
Priya Kapoor
APAC
$127,300
2024-01-28
Zenith Group
Marcus Lee
NA
$91,800
2024-02-15
Stellar Inc
Aisha Diallo
EMEA
$45,200
2024-03-07
Orion Labs
James Wu
NA
$103,600
2024-02-22
Veridian Systems
Lena Petrova
EMEA
$76,900
2024-03-15
Nexus Holdings
Now apply an AutoFilter to Region → select only "EMEA". The SUBTOTAL(9,Table1[Amount]) updates instantly to $184,200 — the sum of Diego ($62,100), Aisha ($45,200), and Lena ($76,900). No manual adjustment needed.
Proof It Works
Below: identical data, same formulas — before and after filtering Region = "EMEA".
Formula
Unfiltered Result
After EMEA Filter
Difference
=SUBTOTAL(9,Table1[Amount])
$591,400
$184,200
✓ Updates
=SUM(Table1[Amount])
$591,400
$591,400
✗ Static
=SUBTOTAL(109,Table1[Amount])
$591,400
$591,400
✗ Ignores only manual hide
=SUBTOTAL(1,Table1[Amount])
$84,485.71
$61,400.00
✓ Updates (AVERAGE)
Exceptions
There are two cases where treating SUBTOTAL as “just a filtered SUM” is correct — and one where it’s dangerously wrong.
Correct use of 109: When summarizing data grouped via Outline (Data → Group → Group), and you want totals to persist across collapsed sections — SUBTOTAL(109) excludes only manually hidden rows, so collapsed groups still contribute.
Correct use of 9 + manual hide: If you’re building a presentation sheet and manually hide rows (right-click → Hide) to simplify view — SUBTOTAL(9) correctly omits those rows, whether filtered or not.
Dangerous assumption: Using SUBTOTAL(9) inside a structured reference like @[Amount] in a calculated column — it returns #VALUE! because SUBTOTAL can’t operate on single-cell relative references inside Tables.
One counterintuitive tip: SUBTOTAL respects row-level filters applied via Power Query only if the data is loaded as a Table and refreshed — but ignores filters applied in PivotTables. So if you feed a PivotTable’s source data into SUBTOTAL, it sees all rows, not just the PivotTable’s visible subset.
Your next step: Open your largest reporting workbook. Find any SUM() formula referencing a column used in filtering. Replace it with =SUBTOTAL(9, [range]). Then apply a filter — watch the number change. If it doesn’t? You’re using 109, or the range includes hidden SUBTOTALs, or the sheet isn’t filtered — not sorted. (Sorting alone won’t trigger SUBTOTAL’s filter-aware behavior.)
Emily Watson
Emily is an expert in workplace culture and team dynamics. Her articles help professionals navigate interpersonal challenges and build better coworker relationships.
