Page 292 - Excel 2007 Bible

19_044039 ch14.qxp 11/21/06 11:06 AM Page 249
Creating Formulas That
Count and Sum
any of the most common spreadsheet questions involve counting and
summing values and other worksheet elements. It seems that people IN THIS CHAPTER
Mare always looking for formulas to count or to sum various items in a Information on counting and
worksheet. If I’ve done my job, this chapter answers the vast majority of such summing cells
questions. It contains many examples that you can easily adapt to your own
situation. Basic counting formulas
Advanced counting formulas
Counting and Summing Worksheet Formulas for performing
Cells common summing tasks
Conditional summing formulas
Generally, a counting formula returns the number of cells in a specified range that using a single criterion
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 Conditional summing formulas
may or may not consist of a worksheet database.
using multiple criteria
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.

CROSS-REF See Chapters 17 and 18 for detailed information and examples of
CROSS-REF
array formulas used for counting and summing.
NOTE If your data is in the form of a table, you can use autofiltering to
NOTE
accomplish many counting and summing operations. Just set the
autofitler 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. Refer to Chapter 6 for more infor-
mation on using tables.

249