Microsoft Office Tutorials and References

In Depth Information

•
Amount
is greater than 1,000

The following formula returns the number of items that meet all three criteria. Note that the COUNTIFS func-

tion uses three sets of pairs of arguments.

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

An alternative formula, which works with versions prior to Excel 2007, 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.

Referring to Figure 7-2, suppose that you want to count the number of sales that meet at least one of the follow-

ing criteria:

•
Month
is January,
or

•
SalesRep
is Brooks,
or

•
Amount
is greater than 1,000

If you attempt to create a formula that uses COUNTIF, some double counting will occur. The solution 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, perhaps you want to

count sales that meet both of the following criteria:

•
Month
is January,
and

•
SalesRep
is Brooks,
or SalesRep
is Cook