Microsoft Office Tutorials and References

In Depth Information

**Counting the most frequently occurring entry**

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 criteria and Or criteria when counting. For

example, perhaps you want to count sales that meet both of the following criteria:

Month is January.

■

SalesRep is Brooks
or SalesRep is Cook.

■

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

17

{=SUM((Month="January")*IF((SalesRep="Brooks")+(SalesRep="Cook"),1))}

Counting the most frequently occurring entry

The
MODE
function returns the most frequently occurring value in a range. Figure 17.3

shows a worksheet with values in range A1:A10 (named Data). The formula that follows

returns
10
because that value appears most frequently in the Data range:

=MODE(Data)

FIGURE 17.3

The
MODE
function returns the most frequently occurring value in a range.

To count the number of times the most frequently occurring value appears in the range (in

other words, the frequency of the mode), use the following formula:

=COUNTIF(Data,MODE(Data))

This formula returns
5
because the modal value (
10
) appears ﬁ ve times in the Data range.