SUBTOTAL function
SUBTOTAL returns a chosen statistic, such as a sum or count, over visible data in a filtered list. It skips other SUBTOTAL results in the range, which prevents double counting.
Example
Read it as a sentence: add up the values in A1:A5 using code 9, which means SUM, which gives 15.
Excel shows 15
A
1
1
2
2
3
3
4
4
5
5
Data
Syntax
SUBTOTAL(function_num, ref1, [ref2], ...)
Arguments in [square brackets] are optional.
-
function_num(required) - 1-11 include manually hidden rows; 101-111 skip them. For example 9/109 is SUM, 1/101 AVERAGE, 2/102 COUNT.
-
ref1(required) - The range to calculate.
-
[ref2], ...(optional) - More ranges.
Returns: The chosen statistic over visible data, ignoring other SUBTOTALs in the range.
What Excel actually returns
Each formula below was run in desktop Excel. The result is exactly what the cell showed, errors included.
| Formula | Excel shows | Case |
|---|---|---|
=SUBTOTAL(9,Data!A1:A5) | 15 | 9 sum |
=SUBTOTAL(109,Data!A1:A5) | 15 | 109 sum |
=SUBTOTAL(1,Data!A1:A5) | 3 | 1 average |
=SUBTOTAL(2,Data!B1:B8) | 3 | 2 count mixed |
=SUBTOTAL(3,Data!B1:B8) | 7 | 3 counta mixed |
=SUBTOTAL(4,Data!A1:A5) | 5 | 4 max |
=SUBTOTAL(0,Data!A1:A5) | #VALUE! | Function 0 |
=SUBTOTAL(12,Data!A1:A5) | #VALUE! | Function 12 |
The Data sheet these formulas read
A cell that starts with = holds that formula.
A
B
1
1
10
2
2
20
3
3
TRUE
4
4
5
5
abc
6
30
7
FALSE
8
40
Data
Watch out for
- Filtered-out rows are always excluded; only manually hidden rows differ between 9 and 109.
- It needs references, not array constants.
- Excel refuses the formula outright if you type an array constant such as {1,2,3} where a range is expected; point it at cells instead (measured in Excel 16.0.20527).
Where it works
- Excel
- SUBTOTAL is in every version of Excel from 2007 on.
- Google Sheets
- Same name, same behaviour for everyday use.
Part of Math and trig functions. Every result on this page was measured in desktop Excel, build 16.0.20527. Last updated .