SUBTOTAL
Returns a subtotal in a list or database, with options to include or exclude hidden values.
SUBTOTAL(function_num, ref1, [ref2], ...)Arguments
function_numFunction number (1-11 or 101-111)ref1First rangeref2Additional ranges(optional)
=SUBTOTAL(9,A1:A100)SUM ignoring filtered rows
=SUBTOTAL(1,B:B)Average of visible cells
=SUBTOTAL(3,C:C)Count visible non-empty
- •1-11: ignores filtered rows; 101-111: also ignores hidden rows
- •9=SUM, 1=AVERAGE, 2=COUNT, 3=COUNTA, 4=MAX, 5=MIN
- •Essential for working with filtered data
- •Using the wrong function number - 1-11 include manually hidden rows, while 101-111 exclude them, and mixing these up gives a subtotal that doesn't match what you expect
- •Forgetting SUBTOTAL ignores other SUBTOTAL formulas already in the range, which is intentional (to avoid double-counting nested subtotals) but confusing if you don't know about it
- •Assuming SUBTOTAL automatically excludes rows hidden by a filter the same way it excludes manually hidden rows - it always excludes filtered-out rows regardless of which function number you use, but only the 101-111 range also excludes manually hidden ones
What's the difference between function numbers 9 and 109 in SUBTOTAL?
Both sum values, but 9 includes manually hidden rows in the total, while 109 excludes them. Both exclude rows hidden by a filter, regardless of which one you use.
Why doesn't my SUBTOTAL formula double-count when there are subtotals within subtotals?
SUBTOTAL automatically ignores other SUBTOTAL formulas already inside its range - this is intentional, to prevent nested subtotals from being counted twice.
Does SUBTOTAL exclude rows hidden by a filter?
Yes, always, no matter which function number you use. The 101-111 range additionally excludes rows hidden manually (right-click > Hide Row), while 1-11 does not.
Need to translate a formula using SUBTOTAL?
Use our translator to convert your complete formula