SUM vs SUBTOTAL: why your filtered totals are wrong
Filter a table and SUM keeps adding the rows you just hid — that's the bug that leads people here. SUBTOTAL(9,...) counts only what's visible after filtering, and it has a second superpower: SUBTOTALs ignore other SUBTOTALs, so section totals plus a grand total never double-count.
The differences at a glance
| SUM | SUBTOTAL | |
|---|---|---|
| Rows hidden by a FILTER | Still included | Excluded (any function code) |
| Rows hidden manually | Still included | Code 9: included · Code 109: excluded |
| Ignores other subtotals in its range | No — double-counts them | Yes — nested totals just work |
| Other aggregates | SUM only | 11 functions by code: 1=AVERAGE, 2=COUNT, 3=COUNTA, 4=MAX, 5=MIN, 9=SUM... |
| Compatibility | Universal | Universal (every version of all three apps we test) |
Which should you use?
- SUM — Fixed totals that should never change with filtering — invoice math, closed reports.
- SUBTOTAL — Anything users filter (SUBTOTAL(9,...) — it's what the Total Row in Excel/Sheets tables inserts), or sheets with section subtotals feeding a grand total. Use 109 when rows are hidden by hand rather than by filter.
Compatibility (from executed tests)
Both execute correctly in every version of all three apps we test, including the 100-series codes. AGGREGATE (Excel/LibreOffice, not Google Sheets) extends the idea with error-ignoring — that one IS a portability decision.
Example formulas
| Filter-aware total | =SUBTOTAL(9,C2:C100) |
| Also ignore manually hidden rows | =SUBTOTAL(109,C2:C100) |
| Visible-row count | =SUBTOTAL(3,A2:A100) |
Full per-version details on each function page: SUM · SUBTOTAL.