The SUBTOTAL function returns subtotals for a range of numbers applying another function.
This function can be useful when working with large data sets and when using filters.
Syntax
SUBTOTAL(function_code, range1, [range2, ...])
Argument |
Description |
Permitted values |
|---|---|---|
function_code |
The numeric code of the function you want to use to calculate the subtotal. Use codes 1-11 to include manually hidden cells in the count, or 101‑111 to exclude them. Cells hidden using a filter are always excluded. |
Numbers from 1 to 11 (excluding hidden ones) and from 101 to 111 (including hidden values) •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 |
range1 |
First range or array over which to calculate a subtotal |
Range of cells or array of numbers |
[range2; ...] |
(optional) Additional ranges or arrays over which to calculate subtotals |
Range of cells or array of numbers |
Examples of use
Sum of values
Calculating the sum of values in the A1:A10 range, ignoring hidden rows:
=SUBTOTAL(9, A1:A10)
Here, 9 corresponds to the SUM function.
Average value
Calculating the average value in the range B1:B10:
=SUBTOTAL(1, B1:B10)
Here, 1 corresponds to the AVERAGE function.
Maximum value
Search for the maximum value in the range C1:C10:
=SUBTOTAL(4, C1:C10)
Here, 4 corresponds to the MAX function.
Notes
–The SUBTOTAL function ignores rows hidden by filters which makes it useful for data analysis.
–Make sure you use the correct function numbers to get the statistical values you need.