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

Custom Search