How do I count the # of unique occurences of a text in a column?

R

Rob Kaiser

I am looking for a command/formulat to count (return a single number) the
number of unique text entries in a column.
 
P

Peo Sjoblom

One way

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


Regards,

Peo Sjoblom
 
D

Duke Carey

This is VERY resource intensive, so be careful using it on a list of several
thousand entries

It's an array formula - enter it with the key combination Ctrl+Shift+Enter

=SUM(1/COUNTIF(A1:A10,A1:A10))
 
A

Aladin Akyurek

It's the SumProduct version of Hager's formula extended to take care of
formula-blanks and empty cells,

{=SUM(IF(A1:A10<>"",1/COUNTIF(A1:A10,A1:A10)))}
 
J

JayMan

Experts
How do you adjust this to account for columns that may be different in
length? One time you need A375 and the next time you use it is A584.


JayMan
 
J

JayMan

T
Do I now plug that in like this, Range is the name of the range
{=SUM(IF(Range<>"",1/COUNTIF(Range,Range)))}

not really sure here

JayMan
 
T

T. Valko

Yes. However, I would use the SUMPRODUCT formula that was posted in your
thread:

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