how can I count distinct names in an excel list?

R

RPC@Frito

I'm using Excel 97 and have a list of names. The entire list is 22,000 rows
or so, and all the names in the list are repeated numerous times (it's
downloaded data). I don't want to delete all the duplicates but want to be
able to count how many distinct names there are in the list.

Any solutions out there?

Thx.
 
P

Paul B

Here is one way,

=COUNT(IF(FREQUENCY(A:A,A:A),1))
--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003
 
P

Peo Sjoblom

Easiest would be to use data>filter>advanced filter, copy to another
location and unique records only, then count the extracted list

=COUNTA(extracted_range)


or use a formula

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


Regards,

Peo Sjoblom
 
P

Paul B

Gord, sorry did not check it on text, but it looks like it does work over a
range with blank cells, on numerics.

Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003
 
Top