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

Search JabSto ::

Custom Search