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

Custom Search