Returns a subtotal for a vertical range of cells using a specified aggregation function.
Sample Usage
SUBTOTAL(1,A2:A5,B2:B8)
Syntax
SUBTOTAL(function_code, range1, [range2, ...])
-
function_code
- The function to use in subtotal aggregation.-
1
isAVERAGE
-
2
isCOUNT
-
3
isCOUNTA
-
4
isMAX
-
5
isMIN
-
6
isPRODUCT
-
7
isSTDEV
-
8
isSTDEVP
-
9
isSUM
-
10
isVAR
-
11
isVARP
-
Hidden values can be skipped for any of these codes by prepending
10
(to the single-digit codes) or1
(to the 2-digit codes). e.g. 102 forCOUNT
while skipping hidden cells, and110
forVAR
while doing so.
-
-
range1
- The first range over which to calculate a subtotal. -
range2, ...
- Additional ranges over which to calculate subtotals.
Notes
-
Cells that are hidden due to autofilter criteria are never included in
SUBTOTAL
, irrespective of thefunction_code
used. -
Cells within any of the specified
range
arguments that containSUBTOTAL
calls are ignored to prevent double-counting. -
SUBTOTAL
can be used to created dynamic dashboards by having the function code argument refer to another cell. When combined with list-based data validation, this cell can become a drop-down list that instantly updates the entire dashboard. -
SUBTOTAL
can be used for quick analysis of different subsets of data by building a subtotal dashboard above a filtered region. Each time the filter criteria change, the dashboard will automatically update with new aggregations. -
Using
SUBTOTAL
helps prevent double-counting associated with simpleSUM
formulas.
See Also
VARP
: Calculates the variance based on an entire population.
VAR
: Calculates the variance based on a sample.
SUM
: Returns the sum of a series of numbers and/or cells.
STDEVP
: Calculates the standard deviation based on an entire population.
STDEV
: The STDEV function calculates the standard deviation based on a sample.
PRODUCT
: Returns the result of multiplying a series of numbers together.
MIN
: Returns the minimum value in a numeric dataset.
MAX
: Returns the maximum value in a numeric dataset.
Returns the number of values in a dataset.
Returns the number of numeric values in a dataset.
AVERAGE
: The AVERAGE function returns the numerical average value in a dataset, ignoring text.