SUBTOTAL in Excel: Totals That Respect Filters
Use SUBTOTAL to sum, count or average only visible rows after filtering, the difference between 9 and 109, and why it ignores other subtotals.
By Ayoub Farid · · 1 min read
SUM adds every row, even ones you've filtered out. SUBTOTAL adds only the rows you can see. That makes it the right total for any filtered list.
Syntax
=SUBTOTAL(function_num, ref1, ...)| Function | Includes manually hidden rows | Ignores manually hidden rows |
|---|---|---|
| AVERAGE | 1 | 101 |
| COUNT | 2 | 102 |
| COUNTA | 3 | 103 |
| MAX | 4 | 104 |
| MIN | 5 | 105 |
| SUM | 9 | 109 |
Both versions ignore rows hidden by a filter. The 100-series also ignores rows you hid by hand (right-click > Hide).
Example
Sales in D2:D500. Above the table:
=SUBTOTAL(9,D2:D500)Filter to "North" and the total shows North only. Clear the filter and it shows everything.
Count visible rows
=SUBTOTAL(3,A2:A500)Shows "how many rows match the filter".
It ignores other SUBTOTALs
If a column contains SUBTOTAL rows for each group, a grand-total SUBTOTAL skips them, so you don't double count. (Data > Subtotal inserts these group rows automatically.)
AGGREGATE: SUBTOTAL plus error handling
=AGGREGATE(9,7,D2:D500)9 = SUM, 7 = ignore hidden rows and errors.
Tables do it for you
In an Excel table (Ctrl+T), Table Design > Total Row adds SUBTOTAL formulas automatically.
Frequently asked questions
How do I sum only visible cells in Excel?
=SUBTOTAL(9,range) (or 109 to also ignore manually hidden rows).
What is the difference between SUBTOTAL 9 and 109?
Both ignore filtered rows; 109 also ignores rows hidden manually.
Why doesn't SUM change when I filter?
SUM includes hidden rows. Use SUBTOTAL instead.
Does SUBTOTAL work in Google Sheets?
Yes, with the same function numbers.