Excel® 2007 Bible

Chapter 14. Creating Formulas That Count and Sum

<feature><title>In This Chapter</title>

• Information on counting and summing cells

• Basic counting formulas

• Advanced counting formulas

• Formulas for performing common summing tasks

• Conditional summing formulas using a single criterion

• Conditional summing formulas using multiple criteria

</feature>

Many of the most common spreadsheet questions involve counting and summing values and other worksheet elements. It seems that people are always looking for formulas to count or to sum various items in a worksheet. If I’ve done my job, this chapter answers the vast majority of such questions. It contains many examples that you can easily adapt to your own situation.

Counting and Summing Worksheet Cells

Generally, a counting formula returns the number of cells in a specified range that meet certain criteria. A summing formula returns the sum of the values of the cells in a range that meet certain criteria. The range you want counted or summed may or may not consist of a worksheet database.

Table 14.1 lists the Excel worksheet functions that come into play when creating counting and summing formulas. Not all these functions are covered in this chapter. If none of the functions in Table 14.1 can solve your problem, it’s likely that an array formula can come to the rescue.

Table 14.1. Excel’s Counting and Summing Functions

Function	Description
COUNT	Returns the number of cells that contain a numeric value
COUNTA	Returns the number of nonblank cells
COUNTBLANK	Returns ...