Microsoft Office Tutorials and References

In Depth Information

The MODE function works only for numeric values, and it ignores cells that contain text. To find the most fre-

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

If there is a tie for the most frequent value, the preceding formula returns only the first

in the list.

Counting the occurrences of specific text

The examples in this section demonstrate various ways to count the occurrences of a character or text string in a

range of cells. Figure 7-4 shows a worksheet that demonstrates these examples. Various text appears in the

range A1:A10 (named
Data
); cell B1 is named
Text.

This book's website contains a workbook named counting text in a range.xlsx that

demonstrates the formulas in this section.