How do i count the number of unique values in a given column?

[Guest]

I have a huge data extract that i'm trying to sort through. The first thing
I want to do is count the number of unique entries in a given column. For
example, in my extract a "name" comes up many times - i want to count how
many "unique" names I have on my data sheet, in a specific column. I'm
pretty sure this is easily done with pivot tables - but can't figure it out
myself. (the pivot table keeps summing stuff up - it's not "counting").

 

[Guest]

Try this kind of formula:

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

Adjust range references to suit your situation.

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP

 

[Domenic]

If you download and install the free add-in Morefunc.xll, you can use
COUNTDIFF...

http://xcell05.free.fr/english/morefunc/countdiff.htm

Otherwise, try...

=SUMPRODUCT((Range<>"")/COUNTIF(Range,Range&""))

Hope this helps!

 

[Guest]

First, thanks so much for your quick reply!

I created a VERY simple spreadshett to test this out. I have 2 columns: A
is "names" and B is "numbers". Under A i have "bobby" listed 3 times and
"susy" listed 1 time. Under B i have 1, 2, 3, 4.

I wasn't sure if the forumla you provided was to be used through pivot
table, so what i did was simply insert it in cell A6 (just below the last
name in column A). I edited the formula to read:
=SUMPRODUCT((A2:A5<>"")/COUNTIF(A2:A5,A2:A5&"")) - it worked PERFECT!!!
Thank you so much!

I wanted to ask you - is the way i have done it what you intended - or were
you thinking of using this formula in a pivot table? somehow?

 

[Guest]

You got it right.....That formula works fine without a pivot table.

(Thanks for letting me know it worked for you)

***********
Regards,
Ron

XL2002, WinXP

 

[Guest]

Thanks again. If i could ask you one more question - what is it in the
formula that determines not to count a "blank" cell? This is exactly what I
want it to do - just curious what part of the formula makes that
determination.

 

[Guest]

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

To give credit where credit is due:
That formula was developed by Aladin Akyurek and Harlan Grove.

Within that formula's context
(A1:A1000<>"") checks if a cell value does not equal an empty string.
It returns a 1 for non-blanks and a 0 for blanks.

In the COUNTIF(A1:A1000,A2:A10&"") section, this part: A1:A1000&"" ensures
that the COUNTIF function will always return at least 1 and never 0 (which
would cause the formula to error out when the division is performed.)

Consequently, the numerator for blank cells is 0, so they are not counted.
The numerator for all non-blanks is 1.
The denominator for all non-blanks is their count.

If a value appears 3 times, three of the numerators will be 1 and their
respective denominators will be 3. The SUMPRODUCT function will add those
three fractions: (1/3)+(1/3)+(1/3)=1
That's how the three occurrences only count as a single instance of a unique
value.


Does that help?
***********
Regards,
Ron

XL2002, WinXP

 

[Guest]

Thanks Ron!

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

To give credit where credit is due:
That formula was developed by Aladin Akyurek and Harlan Grove.

Within that formula's context
(A1:A1000<>"") checks if a cell value does not equal an empty string.
It returns a 1 for non-blanks and a 0 for blanks.

In the COUNTIF(A1:A1000,A2:A10&"") section, this part: A1:A1000&"" ensures
that the COUNTIF function will always return at least 1 and never 0 (which
would cause the formula to error out when the division is performed.)

Consequently, the numerator for blank cells is 0, so they are not counted.
The numerator for all non-blanks is 1.
The denominator for all non-blanks is their count.

If a value appears 3 times, three of the numerators will be 1 and their
respective denominators will be 3. The SUMPRODUCT function will add those
three fractions: (1/3)+(1/3)+(1/3)=1
That's how the three occurrences only count as a single instance of a unique
value.


Does that help?
***********
Regards,
Ron

XL2002, WinXP

 

[Guest]

Thanks Domenic. This is the same suggestion i received from Ron - and it
works pefectly!