Chapter 13: Creating Formulas That Count and Sum
293
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.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 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))}Note
The preceding formula is one of those “classic” Excel formulas that gets passed around the Internet. I don’t
know who originated it. n
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)))}Cross-Reference
To find out how to create an array formula that returns a list of unique items in a range, see Chapter 17. n