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))
 
Search JabSto ::




Custom Search