how can I count distinct names in an excel list?
 

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.

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


"RPC@Frito" wrote in message
...
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.

Easiest would be to use datafilteradvanced 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

"RPC@Frito" wrote in message
...
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

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/duplic...CountingUnique


Gord Dibben Excel MVP

On Wed, 2 Feb 2005 15:53:58 -0500, "Paul B"
wrote:

Here is one way,

=COUNT(IF(FREQUENCY(A:A,A:A),1))

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" <gorddibbATshawDOTca wrote in message
...
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/duplic...CountingUnique


Gord Dibben Excel MVP

On Wed, 2 Feb 2005 15:53:58 -0500, "Paul B"

wrote:

Here is one way,

=COUNT(IF(FREQUENCY(A:A,A:A),1))

Paul

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

Gord

On Thu, 3 Feb 2005 08:55:49 -0500, "Paul B"
wrote:

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" <gorddibbATshawDOTca wrote in message
.. .
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/duplic...CountingUnique


Gord Dibben Excel MVP

On Wed, 2 Feb 2005 15:53:58 -0500, "Paul B"

wrote:

Here is one way,

=COUNT(IF(FREQUENCY(A:A,A:A),1))