Microsoft Office Tutorials and References

In Depth Information

**Advanced Counting Formulas**

Because you have to repeat the And portion of the criteria in each function’s arguments, using

COUNTIFS can produce long formulas with more criteria. When you have a lot of criteria, it

makes sense to use an array formula, like this one that produces the same result:

{=SUM((Month=”January”)*((SalesRep=”Brooks”)+(SalesRep=”Cook”)))}

Counting the most frequently occurring entry

Excel’s MODE function returns the most frequently occurring value in a range. Figure 7-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)

The formula returns an #N/A error if the
Data
range contains no duplicated values.

Figure 7-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 five times in the
Data
range.

The MODE function works only for numeric values, and it ignores cells that contain text. To find

the most frequently occurring text entry in a range, you need to use an array formula.

To count the number of times the most frequently occurring item (text or values) appears in a

range named
Data,
use the following array formula:

{=MAX(COUNTIF(Data,Data))}