How to sum only the filtered (visible) rows
✓ Verified in LibreOffice 25.8.7.3Total that updates to match a filter — sum just the rows currently showing, ignoring anything the AutoFilter has hidden.
The formula
| App | Formula | Notes |
|---|---|---|
| Excel | =SUBTOTAL(9,B2:B100) | 9 = SUM over visible-after-filter rows. Use 109 to also skip rows you hid manually. A plain =SUM would keep counting hidden rows. |
| Google Sheets | =SUBTOTAL(9,B2:B100) | Identical. Sheets' function code 9 also ignores rows hidden by a filter. |
| LibreOffice Calc | =SUBTOTAL(9,B2:B100) | Identical. |
How it works
SUBTOTAL's first argument picks the operation and, crucially, whether hidden rows count. Code 9 means SUM but ignores rows hidden by an AutoFilter, so the total tracks whatever the filter leaves visible — here all four rows are visible, giving 100, but hide any and the result drops automatically. Use 109 instead of 9 to also exclude rows you hid by hand (not via a filter); codes 1/101 do AVERAGE, 2/102 COUNT, 3/103 COUNTA, 4/104 MAX, and so on. A bonus: SUBTOTAL ignores other SUBTOTAL cells in its range, so grand totals don't double-count subtotals. For summing visible cells with a condition on top, AGGREGATE(9,5,range) or a SUMPRODUCT with SUBTOTAL gives finer control.
Verified, not just documented
We ran =SUBTOTAL(9,B2:B5) in LibreOffice 25.8.7.3 (headless, with forced recalculation) and it returned 100 — exactly the expected result. Every formula here is confirmed by actually executing it.
Functions used
SUBTOTAL — see full Excel, Google Sheets & LibreOffice compatibility for each.
Related recipes
- How to count cells by color (and why formulas can't see color)
- How to count only visible rows after filtering