Returns a subtotal for a vertical range of cells using a specified aggregation function.
Sample Usage
SUBTOTAL(9,C2:C5)
SUBTOTAL(1,C2:C5,D2:D8)
Syntax
SUBTOTAL(function_code, range1, [range2])
- function_code - The number that specifies the function to use for the subtotal.
-
1 for AVERAGE
-
2 for COUNT
-
3 for COUNTA
-
4 for MAX
-
5 for MIN
-
6 for PRODUCT
-
7 for STDEV
-
8 for STDEVP
-
9 for SUM
-
10 for VAR
-
11 for VARP
-
- range1 - The first range over which to calculate the subtotal.
- range2 - Additional ranges over which to calculate subtotal(s).
Examples
SUBTOTAL(9,C2:C5) returns the sum of the subtotal of the cells C2:C5.
SUBTOTAL(1,C2:C5,D2:D8) returns the average of the numbers contained in the ranges C2:C5 and D2:D8.
Notes
- If you want to skip the hidden values in your calculation, use the following codes:
- 101 for AVERAGE
- 102 for COUNT
- 103 for COUNTA
- 104 for MAX
- 105 for MIN
- 106 for PRODUCT
- 107 for STDEV
- 108 for STDEVP
- 109 for SUM
- 110 for VAR
- 111 for VARP
- The SUBTOTAL function ignores any rows that are not included in the result of a filter, irrespective of the function_code used.
- Using SUBTOTAL helps prevent double-counting associated with the SUM function. If there are other subtotals within range1, range2,… (or nested subtotals), these nested subtotals are ignored to avoid double counting.
- The SUBTOTAL function is designed for columns of data, or vertical ranges. It is not designed for rows of data, or horizontal ranges. For example, when you subtotal a horizontal range using a function_code of 101 or greater, such as SUBTOTAL(109,B2:G2), hiding a column does not affect the subtotal. But, hiding a row in a subtotal of a vertical range does affect the subtotal.