Microsoft Office Tutorials and References

In Depth Information

**Advanced Counting Formulas**

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
because three cells in
3

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

The SEARCH function returns an error if
Text
is not found in
Data.
The preceding formula counts

one for every cell where SEARCH does not find an error. Because SEARCH is not case sensitive,

neither is this formula.

If you need a case-sensitive count, you can use the following array formula:

{=SUM(IF(LEN(Data)-LEN(SUBSTITUTE(Data,Text,””))>0,1))}

If the Text cells contain the text
Alpha,
the preceding formula returns
because the string
2

appears in two cells (A2 and A8).

Like the SEARCH function, the FIND function returns an error if
Text
is not found in
Data,
as in

this alternative array formula:

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

Unlike SEARCH, the FIND function is case sensitive.

Total occurrences in a range

To count the total number of occurrences of a string within a range of cells, use the following

array formula:

{=(SUM(LEN(Data))-SUM(LEN(SUBSTITUTE(Data,Text,””))))/

LEN(Text)}

If the
Text
cell contains the character
the formula returns
because the range contains seven
7

instances of the string. This formula is case sensitive.