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

Custom Search