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
Search JabSto ::




Custom Search