Microsoft Office Tutorials and References

In Depth Information

**Counting the number of unique values**

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.

17

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
2
because the

string appears in two cells (A2 and A8).

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 modiļ¬ ed 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))}

Useful as it is, this formula does have a serious limitation: If the range contains any blank

cells, it returns an error. The following array formula solves this problem:

{=SUM(IF(COUNTIF(Data,Data)=0,"",1/COUNTIF(Data,Data)))}