Excel functions / Math
SUBTOTAL function
Totals a range, leaving out rows that a filter hides and other subtotals inside it.
Syntax
=SUBTOTAL(function_num, ref1, [ref2], …)
Arguments
function_num: 9 for SUM, 1 AVERAGE, 2 COUNT, 3 COUNTA, 4 MAX, 5 MIN. Add 100 (109, 101…) to also skip rows hidden by hand.ref: The range to total.
When to use it
SUBTOTAL totals a list the way SUM does, with two differences that make it the right choice under a filtered table. It leaves out the rows a filter hides, and it leaves out other SUBTOTAL formulas in its range, so subtotals and a grand total can sit in the same column.
The first argument is a number that picks the calculation: 9 adds up, 1 averages, 2 counts numbers, 3 counts cells that are not empty, 4 is the largest and 5 the smallest. Add 100 (109, 101…) to also leave out rows you hid by hand.
Examples
A total that follows the filter
A B C D E 1 Region Rep Sales 2 North Ana 500 2420 3 South Ben 1200 4 North Chloe 80 5 West Dev 640 In E2:
=SUBTOTAL(9, C2:C5)→ 2420With no filter on, this is the same as SUM. Filter the Region column to North and it shows 580, where
=SUM(C2:C5)would still show 2420.Count the rows that show
A B C D E 1 Region Rep Sales 2 North Ana 500 4 3 South Ben 1200 4 North Chloe 80 5 West Dev 640 In E2:
=SUBTOTAL(3, A2:A5)→ 43counts cells that are not empty, like COUNTA. Under a filter it becomes "how many rows match".Subtotals and a grand total in one column
A B 1 Item Amount 2 Pens 10 3 Paper 20 4 Stationery =SUBTOTAL(9, B2:B3) 5 Ink 5 6 Toner 15 7 Printing =SUBTOTAL(9, B5:B6) 8 50 In B8:
=SUBTOTAL(9, B2:B7)→ 50=SUM(B2:B7)would give 100, because it would add the two subtotals as well as the items.
Common mistakes
- Using SUM under a filtered list. SUM adds every row, hidden or not, so its total does not change when you filter. Use
=SUBTOTAL(9, …)for a total that follows the filter. - Mixing up 9 and 109. Both leave out rows hidden by a filter. Only
109also leaves out rows you hid yourself with Hide Rows. - Giving it values instead of cells. SUBTOTAL works on ranges of cells.
=SUBTOTAL(9, A2:A5*2)and=SUBTOTAL(9, {1,2,3})give #VALUE!.
In Excel, the Total Row of a Table and the Alt+= shortcut under a filtered list both write a SUBTOTAL formula for you.
Common questions
What does 9 mean in SUBTOTAL?
The first argument chooses the calculation, and 9 means sum. So =SUBTOTAL(9, C2:C100) adds up C2:C100, leaving out rows that a filter hides. 1 is average, 2 is count, 3 is count of non-empty cells, 4 is max and 5 is min.
How do I sum only the visible cells in Excel?
Use =SUBTOTAL(109, C2:C100). It adds only the rows that show, whether the others were hidden by a filter or by hand. =SUBTOTAL(9, …) does the same for filtered rows only.
What is the difference between SUBTOTAL and SUM?
SUM adds every cell in the range. SUBTOTAL leaves out rows hidden by a filter and any other SUBTOTAL formulas in the range, and it can also average, count, or find the largest and smallest.
SUBTOTAL in other languages
Excel shows function names in its own language. In these languages it also puts a semicolon between arguments where English has a comma.
- Czech: SUBTOTAL
- Danish: SUBTOTAL
- Dutch: SUBTOTAAL
- Finnish: VÄLISUMMA
- French: SOUS.TOTAL
- German: TEILERGEBNIS
- Hungarian: RÉSZÖSSZEG
- Italian: SUBTOTALE
- Norwegian: DELSUM
- Polish: SUMY.CZĘŚCIOWE
- Portuguese (Brazil): SUBTOTAL
- Portuguese (Portugal): SUBTOTAL
- Russian: ПРОМЕЖУТОЧНЫЕ.ИТОГИ
- Spanish: SUBTOTALES
- Swedish: DELSUMMA
- Turkish: ALTTOPLAM
Try SUBTOTAL in the playground
Practise it
Related functions
- AGGREGATE: Like SUBTOTAL with more calculations, and it can ignore errors in the range.
- SUM: Adds up numbers, cells and ranges.
- SUMIFS: Adds the cells that meet every one of several conditions.
- COUNTA: Counts cells that are not empty.
- FILTER: Keeps only the rows that meet a condition. The result spills.