Microsoft Office Tutorials and References

In Depth Information

**Summarizing worksheets by using hidden and filtered rows**

numbers are summarized in a table later in this section.) The
ref1
,
ref2
, and further

arguments represent up to 29 ranges to include in the calculation.

As an example, assume you have a worksheet where you hid rows 20 through 26

manually. In this case, the formula
=SUBTOTAL(9, C3:C26, E3:E26, G3:G26)
would find the sum of

all values in the ranges C3:C26, E3:E26, and G3:G26, regardless of whether that range

contained 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

(ignores values in

manually hidden

rows)

Operation number

(includes hidden

values)

Function

Description

1

101

AVERAGE

Returns the average of the

values in the range

2

102

COUNT

Counts the cells in the

range that contain a

number

3

103

COUNTA

Counts the nonblank cells

in the range

4

104

MAX

Returns the largest

(maximum) value in the

range

5

105

MIN

Returns the smallest

(minimum) value in the

range

6

106

PRODUCT

Returns the result of

multiplying all numbers in

the range