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.
Search JabSto ::

Custom Search