Microsoft Office Tutorials and References

In Depth Information

**Advanced Counting Formulas**

Counting cells that meet multiple criteria

In many cases, your counting formula will need to count cells only if two or more criteria are met.

These criteria can be based on the cells that are being counted or based on a range of

corresponding cells.

Figure 7-2 shows a simple worksheet that I use for the examples in this section. This sheet shows

sales figures (Amount) categorized by Month, SalesRep, and Type. The worksheet contains

named ranges that correspond to the labels in row 1.

Figure 7-2:
This worksheet demonstrates various counting techniques that use multiple criteria.

The workbook
multiple criteria counting.xlsx
is available on the companion

CD-ROM.

Several of the examples in this section use the COUNTIFS function, which was

introduced in Excel 2007. I also present alternative versions of the formulas, which you

should use if you plan to share your workbook with others who use an earlier version of

Excel.

Using And criteria

An And criterion counts cells if all specified conditions are met. A common example is a formula

that counts the number of values that fall within a numerical range. For example, you may want

to count cells that contain a value greater than 100
and
less than or equal to 200. For this

example, the COUNTIFS function will do the job:

=COUNTIFS(Amount,”>100”,Amount,”<=200”)