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" <RPC@[email protected]> wrote in message
news:[email protected]...
> 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 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

"RPC@Frito" <RPC@[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]>
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
news:[email protected]...
> 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"
> <[email protected]>
> 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" <[email protected]>
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
>news:[email protected]...
>> 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"
>> <[email protected]>
>> wrote:
>>
>>>Here is one way,
>>>
>>>=COUNT(IF(FREQUENCY(A:A,A:A),1))
>>
>