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
formula is used for
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
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).
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) .
The SUM formula will
not recognize filtered
data. Clearly 5+10
does not equal 2,175.