Excel 2010 Bible

Part II: Working with Formulas and Functions

292

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 13.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 13.4

This worksheet demonstrates various ways to count character strings in a range.

On the CD
The companion CD-ROM contains a workbook that demonstrates the formulas in this section. The file is
named counting text in a range.xlsx.

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).

The following array formula is similar to the preceding formula, but this one is case sensitive:

{=SUM(IF(EXACT(Data,Text),1))}

Partial cell contents .......................................................................................

To count the number of cells that contain a string that includes the contents of the Text cell, use

this formula:

=COUNTIF(Data,”*”&Text&”*”)