Excel 2010 Bible

Chapter 13: Creating Formulas That Count and Sum

291

This array formula returns the number of sales that meet the criteria:

{=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 13.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 13.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. It simply 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))}

This next array formula operates like the MODE function except that it works with both text and

values:

{=INDEX(Data,MATCH(MAX(COUNTIF(Data,Data)),COUNTIF(Data,Data),0))}