Microsoft Office Tutorials and References

In Depth Information

You can add two COUNTIFS functions to get the desired result:

=COUNTIFS(Month,”January”,SalesRep,”Brooks”)+

COUNTIFS(Month,”January”,SalesRep,”Cook”)

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 for-

mula, 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.

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))

Figure 7-3:
The MODE function returns the most frequently occurring value in a range.

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