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.

The SUBTOTAL function is designed for columns of data, or vertical ranges. It is not designed for rows of data, or horizontal ranges.
example: : When a horizontal range is subtotaled using a function-number of 101 or greater, hiding a column does not affect the subtotal. However, hiding a row in a subtotal of a vertical range does affect the subtotal.

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.

example: