Microsoft Office Tutorials and References

In Depth Information

**Counting Cells That Meet Multiple Criteria**

Counting Cells That Meet Multiple Criteria

Tip 116 presents examples of formulas that use the COUNTIF function. Those formulas are useful

for counting cells that match a single criterion. The formula examples in this tip are useful when

you 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.

Some of these formulas use the COUNTIFS function, which was introduced in Excel

2007. For compatibility purposes, I also present an alternative formula that works

with previous versions of Excel.

Using “And” criteria

The 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 might want

to count cells that contain a value greater than 0
and
less than or equal to 12. Any cell that has a

positive value less than or equal to 12 is included in the count.

The following formula, which works only in Excel 2007 and later, counts the cells in a range

named Data that fall between 0 and 12:

=COUNTIFS(Data,”>=0”,Data,”<=12”)

Arguments for the COUNTIFS function always come in pairs. This formula has two sets of paired

arguments: The first argument in each pair is the range of interest, and the second argument is

the criterion. Values are counted if they meet
all
criteria specified by each argument pair.

For compatibility with previous versions of Excel, use this formula:

=COUNTIF(Data,”>0”)-COUNTIF(Data,”>12”)

This formula counts the number of values that are greater than 0 and then subtracts the number

of values that are greater than 12. The result is the number of cells that contain a value greater

than 0 and less than or equal to 12.

Creating this type of formula can be confusing because the formula refers to the condition
“>12”

even though the goal is to count values that are less than or equal to 12. An alternative technique is to

use an array formula, such as the following one (you might find creating this type of formula easier):

=SUM((Data>0)*(Data<=12))

When you enter an array formula, press Ctrl+Shift+Enter, not just Enter.