SUBTOTAL calculates a sum, count, average and more while ignoring rows hidden by a filter. It also ignores other SUBTOTALs in the range, so totals never double-count.
Syntax
=SUBTOTAL(function_num, ref1, [ref2], ...)
Argument
What it means
function_num
9 = SUM, 1 = AVERAGE, 2 = COUNT, 3 = COUNTA, 4 = MAX, 5 = MIN. Add 100 (109, 101…) to also ignore manually hidden rows.
ref1
The range to calculate.
Examples
Example 1
=SUBTOTAL(9, E2:E500)
Sum of only the visible (filtered) rows.
Example 2
=SUBTOTAL(3, A2:A500)
How many rows are visible after filtering.
Example 3
=SUBTOTAL(109, E2:E500)
Also skips rows you hid by right-click → Hide.
💡 Excel Tables use SUBTOTAL automatically in their Total Row.
