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.