SUM adds everything in the range, even rows that a filter has hidden. SUBTOTAL adds only what you can see. It is the right function for the total row of any filtered table.
Syntax
=SUBTOTAL(function_num, range)
The numbers to remember
- 9 or 109: SUM
- 1 or 101: AVERAGE
- 2 or 102: COUNT
- 3 or 103: COUNTA
- 4 or 104: MAX
- 5 or 105: MIN
Total of the filtered rows
=SUBTOTAL(109, C2:C500)
Filter the table by region and the total changes with it.
9 or 109?
Both ignore rows hidden by a filter. The 100-series numbers (like 109) also ignore rows you hid by hand with Hide Row. Use 109 when you want only the visible cells in every case.
Nested subtotals
SUBTOTAL ignores other SUBTOTAL formulas inside its range, so grand totals do not count the same numbers twice. This is why the Data, then Subtotal feature uses it.
Count the visible rows
=SUBTOTAL(103, A2:A500) shows how many rows are visible after filtering. Place it above the table as a live counter.
Worked example
=SUBTOTAL(9,B2:B100) sums the visible cells. After you filter the list, it adds only what you see. Use 109 to also skip rows hidden by hand. 1 is average, 2 is count and 4 is max.
Common mistakes
- Using SUM on a filtered list, which still adds hidden rows.
- Mixing 9 and 109 and not knowing why numbers differ.
- Putting the SUBTOTAL formula inside the filtered range.
Frequently asked questions
What is the difference between 9 and 109?
9 includes rows hidden by hand. 109 ignores them. Both skip rows hidden by a filter.
Can SUBTOTAL ignore other SUBTOTALs?
Yes, it skips other SUBTOTAL formulas in the range, so there is no double counting.
Where is the Subtotal command?
On the Data tab, in the Outline group.
Practice this online, free
Reading is only half of learning Excel. Cellgrids is a free place for online Excel practice: solve Excel formula exercises in a live spreadsheet, prepare for Excel interview questions, and work through business case studies built for data analytics and business analytics roles.
Practice these formulas: SUBTOTAL, SUM, COUNTIF.
Start Excel practice → Case studies
