Microsoft Office Tutorials and References
In Depth Information
Chapter 7: Counting and
In This Chapter
• Counting and summing cells
• Counting and summing records in databases and pivot tables
• Basic counting formulas
• Advanced counting formulas
• Formulas for common summing tasks
• Conditional summing formulas using a single criterion
• Conditional summing formulas using multiple criteria
• Using VBA for counting and summing tasks
Many of the most frequently asked spreadsheet questions involve counting and summing values and other work-
sheet elements. It seems that people are always looking for formulas to count or sum various items in a work-
sheet. If I've done my job, this chapter will answer the vast majority of such questions.
Counting and Summing Worksheet Cells
Generally, a counting formula returns the number of cells in a specified range that meet certain criteria. A sum-
ming formula returns the sum of the values of the cells in a range that meet certain criteria. The range that you
want counted or summed may or may not consist of a worksheet database or table.
Table 7-1 lists the worksheet functions that come into play when creating counting and summing formulas. If
none of the functions in Table 7-1 can solve your problem, an array formula can likely come to the rescue.
Table 7-1: Excel Counting and Summing Functions
A versatile function that can be used for counting and summing, with op-
tions to ignore hidden cells, error values, and nested SUBTOTAL or
Returns the number of cells in a range that contain a numeric value.