The SUBTOTAL function
A total that only counts what the filter lets through, and ignores other subtotals: the total you want under a filtered table.
Syntax
=SUBTOTAL(function_num, ref1, [ref2], …)
In a French Excel: =SOUS.TOTAL(no_fonction; réf1; [réf2]; …).
Arguments
- function_num: 9 sum, 1 average, 2 count of numbers, 3 count of values, 4 max, 5 min; add 100 to also ignore rows hidden by hand.
- ref1…: the ranges.
An example
=SUBTOTAL(109,D2:D500)
The sum of the visible rows of column D, filtered or hidden.
Pitfalls
- Codes 1 to 11 count rows hidden by hand (but not filtered ones); 101 to 111 ignore them too.
- Other SUBTOTALs in the range are ignored: no double counting.
Going further
- SUM.
- Automatic pivot table: totals per group, with no filter.
Frequently asked questions
- Why doesn't SUM follow the filter?
- SUM adds up every cell, visible or not. SUBTOTAL is made for filtered tables.