Microsoft Office Tutorials and References

In Depth Information

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 for-

mula.

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
cell contains the text
Alpha,
the preceding formula returns
2
because the string 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 alternat-

ive 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
B,
the formula returns
7
because the range contains seven instances of the

string. This formula is case sensitive.

The following array formula is a modified version that is not case sensitive:

{=(SUM(LEN(Data))-

SUM(LEN(SUBSTITUTE(UPPER(Data),UPPER(Text),””))))/LEN(Text)}

Counting the number of unique values

The following array formula returns the number of unique values in a range named
Data:

{=SUM(1/COUNTIF(Data,Data))}

To understand how this formula works, you need a basic understanding of array formulas. (See Chapter 14 for

an introduction to this topic.) In Figure 7-5, range A1:A12 is named
Data.
Range C1:C12 contains the follow-

ing multicell array formula. A single formula was entered into all 12 cells in the range.

{=COUNTIF(Data,Data)}