# Thread: Count unique text values in a range (Excel 2000)

1. ## Count unique text values in a range (Excel 2000)

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.

2. ## Re: Count unique text values in a range (Excel 2000)

If there ane no blank cells in A1 thru A5, then the formula is not too bad:
<pre>{=SUM(1/COUNTIF(A1:A5, A1:A5))}</pre>

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
<pre>{=SUM(IF(COUNTIF(A1:A5,A1:A5)=0, "", 1/COUNTIF(A1:A5,A1:A5)))}</pre>

again entered as an array formula.

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

3. ## Re: Count unique text values in a range (Excel 2000)

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

4. ## Re: Count unique text values in a range (Excel 2000)

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.