Excel 2010 Bible

Part II: Working with Formulas and Functions

290

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

three criteria. Note that the COUNTIFS function uses three sets of pairs of 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 ............................................................................................

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 13.2.) Suppose that you want to count the number of sales that meet

the following criteria:

l Month is January, or

l (^) SalesRep is Brooks, or
l Amount is greater than 1000
If you attempt to create a formula that uses COUNTIF, some double counting will occur. The solu-
tion is to use an array formula like this:
{=SUM(IF((Month=”January”)+(SalesRep=”Brooks”)+(Amount>1000),1))}

Combining And and Or criteria .....................................................................

In some cases, you may need to combine And and Or criteria when counting. For example, per-

haps you want to count sales that meet the following criteria:

l Month is January, and

l (^) SalesRep is Brooks, or SalesRep is Cook