Microsoft Office Tutorials and References

In Depth Information

**Using Formulas with Filtered Data**

As you learned in Chapter 2,
“Working

with Formulas,” Excel has a number of built-in

formulas for calculating data in your worksheet.

SUM, COUNT, and AVERAGE are three of the

most frequently used and most useful formulas

available.

Figure 8-23

The SUBTOTAL

formula is used for

calculations on

filtered data.

SUBTOTAL Formula

Filtering your data means temporarily hiding the

part of the data in your worksheet that does not

fit the filter criteria and these formulas base

their calculations on the total number of records

in your database, not the filtered records. Figure

8-22 illustrates how formulas like SUM will

return the same number when you have one or

100 records showing in your filtered results.

Table 8-3
SUBTOTAL Functions

Function Number

Formula

1

AVERAGE

Luckily, you can use the SUBTOTAL function

to perform calculations on filtered cells. The

SUBTOTAL function calculates only the visible

cells and ignores all hidden data (see Figure 8-23).

2

COUNT

3

COUNTA

4

MAX

5

MIN

The syntax is =SUBTOTAL(
function number

described in Table 8-3, range of cells to calculate
).

For example, =SUBTOTAL(1,A1:A100) will calculate

the average of the data in cells A1 to A100. To

calculate the sum of the data in the same cells,

type
=SUBTOTAL(9,A1:A100)
.

6

PRODUCT

7

STDEV

8

STDEVP

9

SUM

10

VAR

SUM Formula

11

VARP

Figure 8-22

The SUM formula will

not recognize filtered

data. Clearly 5+10

does not equal 2,175.