Sum only the visible rows after filtering, unlike a plain SUM.
Copy this formula
=SUBTOTAL(109, B2:B100)SUBTOTAL with function code 109 sums a range while ignoring rows hidden by a filter. Codes 1–11 ignore filtered rows; 101–111 also ignore manually hidden rows. Swap 109 for 101 (average), 102 (count), and so on.
=SUBTOTAL(101, B2:B100)→215Average of the visible rows only.
=SUBTOTAL(103, A2:A100)→18Counts the non-empty cells in the visible rows.
=SUBTOTAL(9, B2:B100)→6,450Code 9 skips filtered rows but still includes rows you hid manually.
SUM always totals every row; SUBTOTAL respects your filter, so the total matches what you see.
Both ignore rows hidden by a filter. Codes 101 to 111 also ignore rows you hide manually; codes 1 to 11 include them.
AGGREGATE offers more functions and can also ignore error values. For example, =AGGREGATE(9, 7, B2:B100) sums the visible rows and skips errors.
No. SUBTOTAL ignores other SUBTOTAL formulas inside its range, so a grand total stays correct.
Need a formula for your own data?
Describe what you want in plain language and let ExcelGPT write, explain, and verify the formula — free to start, no plugin.