Microsoft Office Tutorials and References

In Depth Information

Figure 7-4:
This worksheet demonstrates various ways to count characters 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. 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&”*”)

For example, if the
Text
cell contains the text
Alpha
, the formula returns
3
because three cells in the
Data
range

contain the text
alpha
(cells A2, A8, and A10). Note that the comparison is not case sensitive.

An alternative is a longer array formula that uses the SEARCH function:

{=SUM(IF(NOT(ISERROR(SEARCH(text,data))),1))}