Microsoft Office Tutorials and References

In Depth Information

any hidden rows. The formula
=SUBTOTAL(109, C3:C26, E3:E26, G3:G26)
would find the

sum of all values in cells C3:C19, E3:E19, and G3:G19, ignoring the values in the manually

hidden rows.

Important
Be sure to place your
SUBTOTAL
formula in a row that is even with or above the

headers in the range you’re filtering. If you don’t, your filter might hide the formula’s result!

The following table lists the summary operations available for the
SUBTOTAL
formula. Excel

displays the available summary operations as part of the Formula AutoComplete functionality,

so you don’t need to remember the operation numbers or look them up in the Help system.

Operation

number

(includes

hidden

values)

Operation

number

(ignores values

in manually

hidden rows)

Function

Description

1

101

Returns the average of the values in the

range

AVERAGE

2

102

Counts the cells in the range that contain a

number

COUNT

3

103

Counts the nonblank cells in the range

COUNTA

4

104

Returns the largest (maximum) value in the

range

MAX

5

105

Returns the smallest (minimum) value in

the range

MIN

6

106

Returns the result of multiplying all numbers

in the range

PRODUCT

7

107

Calculates the standard deviation of values

in the range by examining a sample of the

values

STDEV.S

8

108

Calculates the standard deviation of the

values in the range by using all the values

STDEV.P

9

109

Returns the result of adding all numbers in

the range together

SUM

10

110

Calculates the variance of values in the

range by examining a sample of the values

VAR.S

11

111

Calculates the variance of the values in the

range by using all of the values

VAR.P

As the previous table shows, the
SUBTOTAL
function has two sets of operations. The first

set (operations 1-11) represents operations that include hidden values in their summary,

and the second set (operations 101-111) represents operations that summarize only values