Microsoft Office Tutorials and References

In Depth Information

**Counting cells based on multiple criteria**

When you enter an array formula, remember to press Ctrl+Shift+Enter, but don’t type the curly brackets. Excel

includes the brackets for you.

Sometimes, the counting criteria will be based on cells other than the cells being counted.

You may, for example, want to count the number of sales that meet all the following

criteria:

Month is January
and

■

SalesRep is Brooks
and

■

Amount is greater than 1,000.

■

The following formula (for Excel 2007 and later) returns the number of items that meet all

three criteria. Note that the
COUNTIFS
function uses three sets of paired arguments.

=COUNTIFS(Month,"January",SalesRep,"Brooks",Amount,">1000")

An alternative formula, which works with all versions of Excel, uses the
SUMPRODUCT

function. The following formula returns the same result as the previous formula:

=SUMPRODUCT((Month="January")*(SalesRep="Brooks")*(Amount>1000))

Yet another way to perform this count is to use an array formula:

{=SUM((Month="January")*(SalesRep="Brooks")*(Amount>1000))}

Using Or criteria

An Or criterion counts cells if any of the multiple conditions is met. To count cells by using

an Or criterion, you can sometimes use multiple
COUNTIF
functions. The following formula,

for example, counts the number of sales made in January
or February:

=COUNTIF(Month,"January")+COUNTIF(Month,"February")

You can also use the
COUNTIF
function in an array formula. The following array formula,

for example, returns the same result as the previous formula:

{=SUM(COUNTIF(Month,{"January","February"}))}

But if you base your Or criteria on cells other than the cells being counted, the
COUNTIF

function won’t work (refer to Figure 17.2). Suppose that you want to count the number of

sales that meet at least one of the following criteria:

Month is January
or

■

SalesRep is Brooks
or

■

Amount is greater than 1,000.

■