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




Custom Search