SUBTOTAL vs SUM: totals that ignore filtered rows
SUM adds every row, even ones a filter hides. SUBTOTAL adds only what you can see. The numbers 9 and 109, and why SUBTOTAL skips other subtotals.
SUM adds every row in its range, even rows a filter hides. SUBTOTAL adds only the rows you can
see. =SUBTOTAL(109,C2:C9) is the total of the visible rows in
C2:C9.
Use SUBTOTAL under any list you filter. Use SUM when the total must always include every row. The rest of this page shows why, on one sheet.
The sheet: expense claims
A finance team exports this month's expense claims. There are 8 claims from 3 teams. The total is in C11, with row 10 left empty.
| Row | A | B | C |
|---|---|---|---|
| 1 | Name | Team | Amount |
| 2 | Ana | Sales | 120 |
| 3 | Ben | Support | 45 |
| 4 | Carl | Sales | 300 |
| 5 | Dina | Finance | 80 |
| 6 | Eli | Support | 60 |
| 7 | Fay | Sales | 95 |
| 8 | Gus | Finance | 150 |
| 9 | Hal | Sales | 210 |
With no filter, =SUM(C2:C9) and
=SUBTOTAL(109,C2:C9) both give 1060. They only differ when rows
are hidden.
Filter to one team
The Sales manager filters column B to Sales. Only Ana, Carl, Fay and Hal are left on screen.
=SUM(C2:C9) still gives 1060. It adds the hidden Support and
Finance claims too.
=SUBTOTAL(109,C2:C9) gives 725. That is 120 + 300 + 95 + 210,
the 4 rows on screen.
The first argument picks the calculation. The second is the range. Change the filter to Finance and the SUBTOTAL changes to 230. The SUM stays at 1060.
Keep an empty row between the data and the total. Then the filter does not treat the total as one more claim.
9 or 109?
Both numbers mean SUM. Both ignore rows that a filter hides. The difference is rows you hide by hand, with right-click and Hide.
- 9 still adds rows you hid by hand.
- 109 ignores them too. It adds only what you can see.
Say Carl's claim of 300 is still being checked. Someone hides row 4 by hand. There is no filter.
=SUBTOTAL(9,C2:C9) gives 1060. It still counts Carl.
=SUBTOTAL(109,C2:C9) gives 760. That matches the rows on
screen.
I use 109 by default, because it always matches what I see. Use 9 when you hide rows only to make the sheet shorter. Then they still count in the total.
The same rule works for the other calculations. Here are the ones people use most.
| Filtered rows ignored | Hidden rows ignored too | Works like |
|---|---|---|
| 1 | 101 | AVERAGE |
| 2 | 102 | COUNT |
| 3 | 103 | COUNTA |
| 4 | 104 | MAX |
| 5 | 105 | MIN |
| 9 | 109 | SUM |
A useful one is 103. =SUBTOTAL(103,A2:A9) counts the names you
can see. With the Sales filter on, it gives 4. Put it next to
=COUNTA(A2:A9), which gives 8. Now the sheet says 4 of 8
claims are showing.
SUBTOTAL skips other subtotals
This is the second reason SUBTOTAL exists. Here the claims are in sections, with a total under
each team. B5 holds =SUBTOTAL(9,B3:B4) and B8 holds
=SUBTOTAL(9,B6:B7).
| Row | A | B |
|---|---|---|
| 1 | Claims by team | |
| 2 | Name | Amount |
| 3 | Ana | 120 |
| 4 | Carl | 300 |
| 5 | Sales total | 420 |
| 6 | Ben | 45 |
| 7 | Eli | 60 |
| 8 | Support total | 105 |
=SUM(B3:B8) gives 1050. It adds the 4 claims and the 2 section
totals, so every claim is counted twice.
=SUBTOTAL(9,B3:B8) gives 525. It skips B5 and B8, because they
are SUBTOTAL formulas.
This only works when the section totals are SUBTOTALs too. If B5 were a SUM, the grand total would count it. Data > Subtotal builds sections like these for you, with SUBTOTAL formulas.
An Excel table does the same. Turn on the Total Row, and Excel writes a SUBTOTAL with 109. So a table total already follows the filter.
Where SUBTOTAL does not help
Hidden columns. SUBTOTAL is made for columns of data. Hide a column, and a SUBTOTAL across a row still adds it.
Errors. If one cell in the range shows #N/A,
SUBTOTAL shows #N/A too. Fix the error if you can. If you cannot,
AGGREGATE can skip it. It works in Excel 2010 and later.
Say Dina's amount in C5 is #N/A, and no rows are hidden.
=AGGREGATE(9,7,C2:C9) gives 980, the other 7 claims.
The 9 means SUM, as in SUBTOTAL. The 7 means ignore hidden rows and error values.
Be careful with this. A total that skips errors also hides them. I would rather see the
#N/A and fix the claim.
The risk: a filter somebody left on
SUBTOTAL follows the filter. That is the point, and it is also the danger. Someone filters to Sales, saves the file and sends it on. The next person reads 725 and thinks it is the month's total.
Look at the row numbers before you trust a SUBTOTAL. Excel shows them in blue when a filter is hiding rows. The gaps in the numbers show where rows are missing.
When one team's total must always be right, do not use a filter at all.
=SUMIF(B2:B9,"Sales",C2:C9) gives 725 whatever the filter shows.
The SUMIF and SUMIFS article has more
practice with it.
Which one to use
-
A list people filter:
=SUBTOTAL(109,range). -
A total that goes to payroll or a report:
=SUM(range), or SUMIF for one group. - Totals under each section, and one at the end: SUBTOTAL for all of them.
The SUBTOTAL lesson below uses a sheet with sections like the one above. You type each formula into a real sheet, and your answer is checked when you press Enter.