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))}
Search JabSto ::




Custom Search