Microsoft® Office 2010 Bible

Chapter 17. Creating Formulas That Count and Sum

IN THIS CHAPTER

Counting and summing cells

Creating basic counting formulas

Entering advanced counting formulas

Using formulas for performing common summing tasks

Adding conditional summing formulas using a single criterion

Creating conditional summing formulas using multiple criteria

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. This chapter attempts to answer 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 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.

Note

If your data is in the form of a table, you can use filtering to accomplish many counting and summing operations. Just set the filter criteria, and the table displays only the rows that match your criteria (the non-qualifying rows in the table are hidden). Then you can select formulas to display counts or sums in the table's ...