Microsoft Office Tutorials and References
In Depth Information
You can add two COUNTIFS functions to get the desired result:
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:
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:
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:
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.