Microsoft Office Tutorials and References
In Depth Information
Counting the occurrences of specific text
The MODE function works only for numeric values. It simply ignores cells that contain
text. To fi nd 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))}
Counting the occurrences of specii c 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 17.4 shows a worksheet used for these
examples. Various text strings appear in the range A1:A10 (named Data); cell B1 is named Text.
FIGURE 17.4
This worksheet demonstrates various ways to count character strings in a range.
Entire cell contents
To count the number of cells containing the contents of the Text cell (and nothing else),
you can use the COUNTIF function as the following formula demonstrates:
=COUNTIF(Data,Text)
For example, if the Text cell contains the string Alpha , the formula returns 2 because two
cells in the Data range contain this text. This formula is not case sensitive, so it counts
both Alpha (cell A2) and alpha (cell A10). Note, however, that it does not count the cell
that contains Alpha Beta (cell A8).
Search JabSto ::




Custom Search