Microsoft Office Tutorials and References

In Depth Information

**Combining “And” and “Or” criteria**

If you base your Or criteria on cells other than the cells being counted, the COUNTIF function

doesn’t work. Refer to Figure 116-1. Suppose that you want to count the number of sales that

meet one of the following criteria:

h
Month is January,
or

h
SalesRep is Brooks,
or

h
Amount is greater than 1000

The following array formula returns the correct count:

=SUM(IF((Month=”January”)+(SalesRep=”Brooks”)+(Amount>1000),1))

Combining “And” and “Or” criteria

You can combine And and Or criteria when counting. Perhaps you want to count sales that meet

the following criteria:

h
Month is January,
and

h
SalesRep is Brooks,
or

h
SalesRep is Cook

This array formula returns the number of sales that meet the criteria:

=SUM((Month=”January”)*IF((SalesRep=”Brooks”)+(SalesRep=”Cook”),1))