how can I count distinct names in an excel list?

[Guest]

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.

 

[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

 

[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

 

[Gord Dibben]

Paul

This is the second time you have posted this formula in two days.

Have you tried it on any column of data other than numerics?

Returns 0 for me and the Help states it won't work on blanks or text cells.

Frito

Suggest you look at Chip Pearson's site......

http://www.cpearson.com/excel/duplicat.htm#CountingUnique


Gord Dibben Excel MVP

 

[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

 

[Gord Dibben]

Paul

Correct. Does work with blanks it the range. My bad.

Gord