Microsoft Office Tutorials and References
In Depth Information
Using COUNTIF, AVERAGEIF, and SUMIF to Conditionally Count, Average, or Sum Data
COUNTIF ,, AVERAGEIF
AVERAGEIF ,, and
SUMIF tto Conditionally
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:
Figure 11.32. COUNTIF
SUMIF are simpler to use than
are simpler to use than DSUM
SUMPRODUCT, or array formulas.
, or array formulas.