Microsoft Office Tutorials and References

In Depth Information

Chapter 7: Counting and

Summing Techniques

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

Function

Description

A versatile function that can be used for counting and summing, with op-

tions to ignore hidden cells, error values, and nested SUBTOTAL or

AGGREGATE functions.

AGGREGATE

COUNT

Returns the number of cells in a range that contain a numeric value.