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 (documented across all versions; executed in every LibreOffice version 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 (Excel for the web, Sheets & LibreOffice executed; desktop Excel per docs)
Both execute correctly in every LibreOffice version we test (desktop Excel behavior per docs — we do not run desktop Excel; Google Sheets is executed separately, see each function page), including the 100-series codes. AGGREGATE (Excel/LibreOffice, not Google Sheets) extends the idea with error-ignoring — that one IS a portability decision. Excel for the web is a separate application with its own calculation engine, and that one we do execute: all 7 corpus cases for these functions matched the documented values there (recalculated on OneDrive, 2026-09-01); the per-case values are on the individual function pages.
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.