Microsoft Office Tutorials and References

In Depth Information

**Using COUNTIF, AVERAGEIF, and SUMIF to Conditionally Count, Average, or Sum Data**

Using

Using
COUNTIF

COUNTIF
,,
AVERAGEIF

AVERAGEIF
,, and

and
SUMIF

SUMIF
tto Conditionally

Conditionally Count,

Count, Average,

Average, oor

Sum Data

Sum Data

The COUNTIF and SUMIF functions are young and popular. In contrast to

most functions that have been around since the 1980s, these functions were

added in Excel 97. The AVERAGEIF function is even newer, having been added in

Excel 2007. Math purists might point out that you could perform equivalent

calculations by using DSUM, SUMPRODUCT, or even an array formula long

before Microsoft added these functions. However, it is far easier to grasp

doing calculations with COUNTIF, AVERAGEIF, and SUMIF.

Figure 11.32
shows a database that contains thousands of records. Your

goal is to find out how many records came from each region. One way to

write the formula for the East region is =COUNTIF($C$11:$C$5011,"East").

However, it is far more interesting to write the formula as shown in cell B2:

=COUNTIF($C$11:$C$5011,A2)

Figure 11.32.

Figure 11.32.
COUNTIF

COUNTIF and

and SUMIF

SUMIF are simpler to use than

are simpler to use than DSUM

DSUM,,

SUMPRODUCT

SUMPRODUCT, or array formulas.

, or array formulas.