Microsoft Office Tutorials and References

In Depth Information

**Chapter 17: Creating Formulas That Count and Sum**

17

Creating Formulas That Count

and Sum

IN THIS CHAPTER

Introducing various ways to count and sum cells

Creating basic counting and summing formulas

Working with advanced counting and summing formulas

Developing conditional summing formulas

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 speciﬁ ed range that meet certain criteria.

A
summing formula
returns the sum of the values of the cells in a range that meet certain criteria.

Table 17.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 17.1 can solve your problem, it’s likely that an array formula can come to the rescue; refer to

Excel’s Help to learn more about array formulas.

If your data is in the form of a table, you can use i ltering to accomplish many counting and summing operations. Just

set the i filter criteria, and the table displays only the rows that match your criteria (the nonqualifying rows in the table

are hidden). Then you can select formulas to display counts or sums in the table’s total row. Chapter 19 introduces

tables.