Count unique text values in a range (Excel 2000)

Topic

New Reply

How do I count the number of UNIQUE text values in a range? For example, cells A1 through A5 have the following values: apples, apples, oranges, peaches, bananas. The count of unique text values is 4. What kind of formula can I use to get this calculation?

Any help is much appreciated. Thanks.

Reply | Quote

Replies

If there ane no blank cells in A1 thru A5, then the formula is not too bad:

{=SUM(1/COUNTIF(A1:A5, A1:A5))}

where the braces are placed there by Excel as you enter this as an array formula (press Ctrl-Shift-Enter instead of Enter)

If there can be blanks, then it is even stranger

{=SUM(IF(COUNTIF(A1:A5,A1:A5)=0, "", 1/COUNTIF(A1:A5,A1:A5)))}

again entered as an array formula.

No, I’m not the genius, this is all on John Walkenbach’s site j-walk.com –Sam

Reply | Quote

You’re a doll, even if you did get it from j-walk.com.
Thanks,
Linda

Reply | Quote

In case of (formula-generated) blanks I’d suggest using:

=SUM(IF(LEN(A1:A5),1/COUNTIF(A1:A5,A1:A5)))

array-entered of course.

The particular use of COUNTIF is an invention of David Hager. Enforcing it with the LEN test originates from my efforts in fighting the formula-generated blanks.

Aladin

Reply | Quote