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.

Search JabSto ::

Custom Search