Microsoft Office Professional 2010 Step by Step eBook

356 Chapter 12 Focus on Specific Data by Using Filters

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 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

7 107 STDEV.S Calculates the standard deviation of values

in the range by examining a sample of the

values

8 108 STDEV.P Calculates the standard deviation of the

values in the range by using all the values

9 109 SUM Returns the result of adding all numbers in

the range together

10 110 VAR.S Calculates the variance of values in the

range by examining a sample of the values

11 111 VAR.P Calculates the variance of the values in the

range by using all of the values

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