What it does
SUBTOTAL calculates summaries that can ignore filtered rows.
Syntax or pattern
=SUBTOTAL(function_num, ref1, [ref2], ...)✅
5 practical examples
1
Sum visible rows
Sum only rows visible after filtering.
=SUBTOTAL(109,Sales[Amount])109 sums visible values.
2
Count visible rows
Count visible numeric values.
=SUBTOTAL(102,B2:B100)Useful with filters.
3
Average visible rows
Average filtered results.
=SUBTOTAL(101,Sales[Amount])Changes as filters change.
4
Find max visible value
Return maximum among visible rows.
=SUBTOTAL(104,B2:B100)Good for filtered reports.
5
Use in a table total row
Add a dynamic total at the bottom of a table.
=SUBTOTAL(109,[Amount])Excel Tables often use this pattern.
Common mistakes to avoid
- Selecting ranges with different sizes.
- Using text values where numbers are required.
- Forgetting how blanks and hidden rows are treated.
Related Excel examples
FAQ
Can this formula use Excel Tables?
Yes. Structured references often make summary formulas easier to read.