SUBTOTAL aggregates a range while ignoring whatever a filter has hidden. =SUBTOTAL(9, B2:B20) sums only the rows a filter left on screen, where a plain =SUM(B2:B20) would add every row, hidden ones included. The first argument, function_num, picks the operation: 9 is SUM, 1 is AVERAGE, 2 and 3 count entries. Each operation also has a 100-series twin (109, 101, 102) that does the same math but drops rows you hid by hand as well.
You can let Excel write these formulas for you: Data > Outline > Subtotal inserts SUBTOTAL formulas at each group break in a sorted list. This page is about the function itself, which you can type anywhere a total belongs.
How does SUBTOTAL work?
function_num is required and comes first: the code that names the operation. ref1 is the range to aggregate, and you can name more ranges after it, up to 254 in all. Each operation carries two codes, a 1-series and a 101-series version, that run the same math and split only on manually hidden rows:
| function_num (1-11) | function_num (101-111) | Operation |
|---|---|---|
| 1 | 101 | AVERAGE |
| 2 | 102 | COUNT |
| 3 | 103 | COUNTA |
| 4 | 104 | MAX |
| 5 | 105 | MIN |
| 6 | 106 | PRODUCT |
| 7 | 107 | STDEV |
| 8 | 108 | STDEVP |
| 9 | 109 | SUM |
| 10 | 110 | VAR |
| 11 | 111 | VARP |
So 9 and 109 both SUM, 1 and 101 both AVERAGE, and 2, 3, 102, and 103 are the count variants: 2 and 102 count numbers, 3 and 103 count any non-blank cell.
SUBTOTAL follows the filter; SUM does not
This is the reason to pick SUBTOTAL over SUM. Filter a list and a SUBTOTAL over the same range totals only the rows still showing:
| A | B | C | |
|---|---|---|---|
| 1 | Rep | Region | Sales |
| 2 | Ada | North | 120 |
| 4 | Cy | North | 150 |
| 6 | Eve | North | 23 |
| 7 | SUBTOTAL(9) | 293 | |
| 8 | Plain SUM | 350 |
Whatever function_num you pass, SUBTOTAL leaves out every row the filter is hiding, and the number re-totals as you change the filter. A plain SUM has no idea a filter is on, so it keeps adding the rows that scrolled out of view. This is about following a filter, not about totaling rows that meet a condition regardless of what is on screen; for that, SUMIF and COUNTIF test a condition instead of tracking the view.
1-11 or 101-111: rows you hide by hand
A filter is not the only way a row leaves the screen. Right-click a row number and choose Hide and you have hidden it by hand, and that is the single case where the two code families part ways. Take a column holding 120, 10, 150, and 23, then hide the row with 150 in it:
=SUBTOTAL(9, B2:B5) returns 303, since codes 1 to 11 still count the hidden 150.
=SUBTOTAL(109, B2:B5) returns 153, since codes 101 to 111 leave it out.
Had a filter hidden that 150 row instead, both formulas would return 153, because a filter drops the row for every function_num. The 9-versus-109 split only shows up for rows you hid yourself. The shortcut to remember: add 100 to the code when you want manually hidden rows gone too.
Why SUBTOTAL does not double-count nested totals
When the range you point SUBTOTAL at already holds its own SUBTOTAL results, the inner ones are skipped, so a grand total sitting above a set of group totals never counts the same figures twice. That is what makes the Data > Outline > Subtotal outline safe: it stacks a SUBTOTAL at each group break and one more across the whole list, and only the raw rows get added.
Two smaller points. SUBTOTAL is built for a column of values, a vertical range; aim a 101-series SUBTOTAL across a row and hiding a column will not change what it returns. And AGGREGATE is the larger sibling for when you also need the total to skip error cells inside the range, with more options than SUBTOTAL carries.
The short version
SUBTOTAL(function_num, ref1, [ref2], ...)totals a range but skips the rows a filter hid; a plain SUM does not.function_numpicks the operation: 9 sums, 1 averages, 2 and 3 count. The full map holds 11 operations, each with a 1-series code and a 101-series code.- Codes 1 to 11 keep rows you manually hid in the total; codes 101 to 111 drop them; both always drop filtered rows. So under a filter, 9 and 109 agree.
- SUBTOTAL ignores any SUBTOTAL results already inside its range, so the Data > Outline > Subtotal outline never double-counts.
- Related: the count functions for COUNT and COUNTA on their own, filtering for how the view drives the total, SUMIF and COUNTIF for totals that follow a condition rather than a filter, and AutoSum for the button behind the one-click total, whose Total Row drop-down writes this SUBTOTAL.
- When a total moves after someone edits a shared workbook, compare the two versions to see whether the data changed or only the
function_numdid.
