SUBTOTAL
Syntax:
SUBTOTAL ( function-number , argument-list )
Description: Computes a value using the function designated by function-number, using the arguments in argument-list.
Arguments:
Name |
Type |
Description |
---|---|---|
function-number |
number |
Indicates the function to be called, as shown in the table below. |
argument-list |
number |
Each argument in argument-list is passed to the called function, in the order specified. That shall be no more than 254 arguments. |
function-number(includes hidden values) |
function-number(excludes hidden values) |
Function |
---|---|---|
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 |
If any argument contains a SUBTOTAL
function call, that call shall be ignored to avoid double counting.
For the function-number values 1–11, the values of hidden rows are included. For the function-number values 101–111, the values of hidden rows are excluded.
The SUBTOTAL
function shall ignore any rows that are not included in the result of a filter, regardless of which function-number value is used.
SUBTOTAL
function is designed for columns of data, or vertical ranges. It is not designed for rows of data, or horizontal ranges. Return Type and Value: number – The result from calling the function designated by function-number, using the arguments in argument-list.
However, if function-number does not have one of the values specified above, #NUM!
is returned.