=SUBTOTAL(function_num, range) totals, averages or counts a list while ignoring any other SUBTOTAL formulas inside the range, so nothing gets counted twice. Use 9 for SUM or 1 for AVERAGE, or 109 and 101 if you want rows you've hidden by hand left out.
Microsoft describes SUBTOTAL as returning a subtotal in a list or database. The syntax is SUBTOTAL(function_num, ref1, ref2, ...), where ref1 is required and you can add more ranges after it. The function number decides what kind of calculation it does.
A quick example
Microsoft's example has four numbers, 120, 10, 150 and 23, in cells A2 to A5.
- =SUBTOTAL(9,A2:A5) returns 303, the sum.
- =SUBTOTAL(1,A2:A5) returns 75.75, the average.
So far it looks like SUM and AVERAGE with an extra number. The difference shows up when your data has hidden rows, filters or other subtotals in it.
The SUBTOTAL function numbers
There are 11 calculations, each with two numbers. Here's Microsoft's full list:
| Function | Includes manually hidden rows | Ignores manually hidden rows |
|---|---|---|
| AVERAGE | 1 | 101 |
| COUNT | 2 | 102 |
| COUNTA | 3 | 103 |
| MAX | 4 | 104 |
| MIN | 5 | 105 |
| PRODUCT | 6 | 106 |
| STDEV | 7 | 107 |
| STDEVP | 8 | 108 |
| SUM | 9 | 109 |
| VAR | 10 | 110 |
| VARP | 11 | 111 |
The catch: hidden rows and filtered rows aren't the same
This is the part that confuses people, and Microsoft spells it out. Numbers 1 to 11 include values in rows you've hidden manually. Numbers 101 to 111 leave those rows out. But either way, SUBTOTAL ignores any rows that a filter has removed from view.
So if you filter a list, 9 and 109 give you the same answer, because both skip the filtered-out rows. If you hide rows by right-clicking and choosing Hide, only 109 leaves them out. That's why SUBTOTAL is the formula to put under a filtered list: the total changes as the filter changes, which a plain SUM won't do. If you're new to filters, here's how to filter in Excel.
Two more rules from Microsoft. SUBTOTAL is built for columns of data, so hiding a column doesn't affect a formula like SUBTOTAL(109,B2:G2) that runs across a row. And if the range contains other subtotals, nested inside it, they're ignored to avoid double counting. A 3-D reference across several sheets returns #VALUE!.
To go a step further, try how to use the IF function in Excel.
How to add subtotals with the Data tab
You don't have to type SUBTOTAL formulas yourself. Microsoft says it's generally easier to use the Subtotal command in the Outline group on the Data tab of the desktop app, then edit the SUBTOTAL formulas it creates if you need to. Before you start, make sure each column has a label in the first row, each column holds similar facts, and there are no blank rows or columns in the range.
- Sort the column you want to group by: Data tab, Sort and Filter, Sort A to Z.
- Go to the Data tab, Outline group, and click Subtotal.
- In At each change in, choose the column you sorted.
- In Use function, choose the calculation, such as Sum.
- In Add subtotal to, tick the columns you want totalled.
- Choose whether to add a page break between groups and whether the summary row goes above or below, then click OK.
- Use the level buttons to show or hide the detail rows.
CFI's example is a garment maker with T-shirts in five colours and seven sizes, stored across two warehouses. Sort by colour, then run Subtotal with At each change in set to Color, Use function set to Sum, and the units in Warehouse I and Warehouse II ticked. Excel inserts a subtotal row below each colour, and the level buttons 1, 2 and 3 let you collapse the list to just the totals. Grouping rows by hand works differently; here's how to group rows in Excel if that's what you're after.
Want AI doing the heavy lifting in your marketing?
I build the systems that handle the boring 80 percent, so you get your week back. Done properly, with the human kept in.
Things that trip people up with the Subtotal command
- It's greyed out in an Excel table. Microsoft says subtotals aren't supported in tables, so convert the table to a normal range first, which removes table features except the formatting, or use a PivotTable instead.
- Grand totals come from the detail rows, not from the subtotal rows. So an Average grand total is the average of every detail row, not an average of the subtotals.
- If you filter data that contains subtotals, the subtotals can disappear from view. Clear all filters to see them again.
- To add a second level, such as subtotals within subtotals, run the command again with Replace current subtotals cleared.
- To take them off, open the Subtotal box again and click Remove All.
With automatic calculation switched on, Microsoft says the subtotals recalculate as you edit the detail data, and the list is outlined so you can show or hide the detail.
Related reading: the full answer on how to use SUMPRODUCT in Excel.
My verdict
Use 109, not 9, when you hide rows by hand and want them left out of the total. If you only ever filter, either works. I'd use SUBTOTAL under any list you plan to filter, because it's the one total that stays honest as you change what's showing. And if you're summarising a big table rather than a sorted list, a pivot table is usually the better tool.
Frequently asked questions
What is the difference between SUBTOTAL 9 and 109?
Both sum. 9 includes rows you've hidden manually; 109 leaves them out. Both ignore rows removed by a filter.
Does SUBTOTAL ignore filtered rows?
Yes. Microsoft says SUBTOTAL ignores any rows not included in the result of a filter, whichever function number you use.
Why is the Subtotal button greyed out?
Because the data is in an Excel table. Microsoft says to convert it to a normal range or use a PivotTable.
Does SUBTOTAL double count other subtotals?
No. If the range contains other subtotals, Microsoft says they're ignored to avoid double counting.
How do I remove subtotals in Excel?
Open the Subtotal box on the Data tab again and click Remove All.
