Formula to count unique values in a range

[Dave K]

Is there a formula that will count the number of unique values in a
given range.

In this instance, my range has both letters and numbers.

For example.

A234
A234
A998
A961

I need a formula that will count the above and produce the number 3.

Thanks in advance for any suggestions.

 

[Ron Coderre]

Try this:

=SUMPRODUCT((A1:A10<>"")/COUNTIF(A1:A10,A1:A10&""))

Is that something you can work with?
Post back if you have more questions.
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

 

[Alan]

Not sure if you've done a typo here or I'm missing something, but I can only
see two unique values, ie A234. If that's the case try
=COUNTIF(B1:B100,"A234")
Or if you want to put A234 in a cell, say E1.
COUNTIF(B1:B100,E1)
Change the ranges to suit,
Regards,
Alan.

 

[Tyro]

There are 3 unique values in the range: A234, A998, A961. Ron's formula
returns 3. Perhaps you're confusing unique and duplicate?

Tyro

 

[Alan]

You mean 9? If that's whats required then great. I must be getting old! Odd
way to ask the question but if the end result is what's needed then I
bow!!!!
Regards,
Alan

 

[Tyro]

There are 4 values in the column. A234, A234, A998, A961. Of the 4 values,
3 are unique: A234, A998 and A961. There is 1 duplicate: A234.
The OP asked how to compute the number of unique entries. Ron's formula
returns 3 which is correct. I have no idea where you get 9 from.

Tyro

 

[Alan]

Of course, obtuse thinking on my part, apologies,
Alan.

 

[Bernd P]

Hello,

I know that this worksheet function solution is almost common sense
here. It has been suggested quite often.

But: Its runtime is acceptable only for a small amount of rows.

If you copy my UDF lfreq (http://www.sulprobil.com/html/listfreq.html)
you could count unique entries with
=ROWS(lfreq(A1:A10))

For 10 rows FastExcel shows these runtimes:
SUMPRODUCT/COUNTIF 0.12 msec
ROWS/lfreq 2 msec

For 100 rows:
SUMPRODUCT/COUNTIF 1.2 msec
ROWS/lfreq 3.5 msec

For 1000 rows:
SUMPRODUCT/COUNTIF 85 msec
ROWS/lfreq 15 msec

Now for 10,000 rows we get:
SUMPRODUCT/COUNTIF 8,400.00 msec
ROWS/lfreq 125 msec

Meaning: You will wait 8 seconds for the answer of ONE single cell
results with the SUMPRODUCT/COUNTIF approach for 10,000 rows.

Regards,
Bernd

 

[T. Valko]

Here's what I did...

I filled A1:A10000 with the numbers 1 : 10000

You will wait 8 seconds for the answer

On my machine, which is basically gutless by "todays" standards, it took on
average 5.3 secs measured using Charles Williams' rangetimer method.

It took on average *46* secs using the UDF and formula
=ROWS(lfreq(A1:A10000))

screencap:

http://img517.imageshack.us/img517/3247/lfreqhj2.jpg

 

[Bernd P]

Hi Biff,

Interesting.

Since the SUMPRODUCT/COUNTIF approach takes quadratic runtime and the
ROWS(lfreq) only linear, there has be to be a cut-off/break even
point, I believe.

I think the "repeat"-ratio determines this cut-off-point.

Your test data does not show any duplicates, right?

Regards,
Bernd

 

[T. Valko]

Your test data does not show any duplicates, right?

Right, no duplicates. 10,000 unique entries which the formula correctly
calculates.

The rangetimer routine is available here:

http://msdn2.microsoft.com/en-us/library/aa730921.aspx

It's probably the same routine used in FastExcel.

 

[Bernd P]

Hello again,

IMHO Charles Williams' UDF countu should be taken:
http://msdn2.microsoft.com/en-us/library/aa730921.aspx

Regards,
Bernd