Microsoft Office Tutorials and References
In Depth Information
Using “Or” criteria
For compatibility with previous versions of Excel, use the following formula to count the number
of items that meet all three criteria:
=SUMPRODUCT((Month=”January”)*(SalesRep=”Brooks”)*(Amount>1000))
If your data is in a table (created by choosing Insert➜Tables➜Table), you can use
filtering to display only the rows that meet multiple And criteria. Figure 116-2 shows the
data filtered to show only the January sales for Brooks that exceed 1000. I also added
a Total Row to this table, which displays the count. However, filtering cannot be used
for Or criteria, which is discussed next.
Figure 116-2: Using table filtering to count rows that meet multiple And criteria.
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 instances of 1, 3, and 5 in the range named
Data:
=COUNTIF(Data,1)+COUNTIF(Data,3)+COUNTIF(Data,5)
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(Data,{1,3,5}))
Enter an array formula by using Ctrl+Shift+Enter.
 
Search JabSto ::




Custom Search