SUBTOTAL Generator
Sum, count, or average a range while skipping rows removed by a filter.
SUBTOTAL
Sum, count, or average a range while skipping rows removed by a filter.
9 = SUM including manually hidden rows, 109 = SUM excluding them. See the tips for the full 1-11 / 101-111 list.
The range to aggregate. Keep it to the data rows only, not the header or the total row.
=SUBTOTAL(109, B2:B100)Worked Example
A filtered sales table with amounts in B2:B100, and you want a total that follows the filter.
=SUBTOTAL(109, B2:B100)Returns: Returns the sum of B2:B100 counting only visible rows. Filter the table down to the West region and the number immediately drops to the West-only total; rows you hid by hand are skipped too.
Checks Before You Paste
- •Both families skip rows hidden by an AutoFilter, so filtering behaves the same either way. The difference is manual hiding: 1-11 (9 = SUM) still includes rows you hid by hand, while 101-111 (109 = SUM) leaves them out.
- •SUBTOTAL only ignores hidden ROWS. Hiding a column changes nothing, so 109 across a horizontal range still adds the values in the columns you hid.
- •SUBTOTAL ignores any nested SUBTOTAL inside its own range, so a grand total over a column that already contains section subtotals will not double-count them. A plain SUM in the same place would.