Remember Me?

#1
February 19th 08, 12:34 AM posted to microsoft.public.excel.misc
 external usenet poster First recorded activity by ExcelBanter: May 2007 Posts: 23
how can i count the number of unique names in a list

I hace a list with multiple same names. in excel how can i count each name
once for a get a list total . I tried most of the count function and can get
them to work.
smith
jones
smith
west
east
west
total 5 names.

Thank you

#2
February 19th 08, 12:42 AM posted to microsoft.public.excel.misc
 external usenet poster First recorded activity by ExcelBanter: Jul 2006 Posts: 2,118
how can i count the number of unique names in a list

This formula returns the count of unique items:

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

In your posted example, 5 names are listed, but smith is listed twice.
Consequently, there are 4 unique names.

Does that help?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

"Anvil22" wrote in message
news
I hace a list with multiple same names. in excel how can i count each name
once for a get a list total . I tried most of the count function and can
get
them to work.
smith
jones
smith
west
east
west
total 5 names.

Thank you

#3
February 19th 08, 12:46 AM posted to microsoft.public.excel.misc
 external usenet poster First recorded activity by ExcelBanter: Jul 2006 Posts: 11,058
how can i count the number of unique names in a list

See:

http://www.cpearson.com/excel/Duplicates.aspx
--
Gary''s Student - gsnu200769

"Anvil22" wrote:

I hace a list with multiple same names. in excel how can i count each name
once for a get a list total . I tried most of the count function and can get
them to work.
smith
jones
smith
west
east
west
total 5 names.

Thank you

#4
February 19th 08, 12:54 AM posted to microsoft.public.excel.misc
 external usenet poster First recorded activity by ExcelBanter: Jul 2006 Posts: 2,118
how can i count the number of unique names in a list

Not that it's important...but, my comment should have been:

In your posted example, 6 names are listed,
but smith and west are listed twice.
Consequently, there are 4 unique names.

--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

"Ron Coderre" wrote in message
...
This formula returns the count of unique items:

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

In your posted example, 5 names are listed, but smith is listed twice.
Consequently, there are 4 unique names.

Does that help?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

"Anvil22" wrote in message
news
I hace a list with multiple same names. in excel how can i count each
name
once for a get a list total . I tried most of the count function and can
get
them to work.
smith
jones
smith
west
east
west
total 5 names.

Thank you

#5
February 19th 08, 01:06 AM posted to microsoft.public.excel.misc
 external usenet poster First recorded activity by ExcelBanter: May 2007 Posts: 23
how can i count the number of unique names in a list

Ron,
It worked - greatly appreciated- thank you.

"Ron Coderre" wrote:

Not that it's important...but, my comment should have been:

In your posted example, 6 names are listed,
but smith and west are listed twice.
Consequently, there are 4 unique names.

--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

"Ron Coderre" wrote in message
...
This formula returns the count of unique items:

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

In your posted example, 5 names are listed, but smith is listed twice.
Consequently, there are 4 unique names.

Does that help?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

"Anvil22" wrote in message
news
I hace a list with multiple same names. in excel how can i count each
name
once for a get a list total . I tried most of the count function and can
get
them to work.
smith
jones
smith
west
east
west
total 5 names.

Thank you

#6
February 19th 08, 01:09 AM posted to microsoft.public.excel.misc
 external usenet poster First recorded activity by ExcelBanter: May 2007 Posts: 23
how can i count the number of unique names in a list

Gary,
Dick

"Gary''s Student" wrote:

See:

http://www.cpearson.com/excel/Duplicates.aspx
--
Gary''s Student - gsnu200769

"Anvil22" wrote:

I hace a list with multiple same names. in excel how can i count each name
once for a get a list total . I tried most of the count function and can get
them to work.
smith
jones
smith
west
east
west
total 5 names.

Thank you

#7
February 19th 08, 01:36 AM posted to microsoft.public.excel.misc
 external usenet poster First recorded activity by ExcelBanter: Jul 2006 Posts: 2,118
how can i count the number of unique names in a list

You're very welcome......I'm glad I could help.

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

"Anvil22" wrote in message
...
Ron,
It worked - greatly appreciated- thank you.

"Ron Coderre" wrote:

Not that it's important...but, my comment should have been:

In your posted example, 6 names are listed,
but smith and west are listed twice.
Consequently, there are 4 unique names.

--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

"Ron Coderre" wrote in message
...
This formula returns the count of unique items:

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

In your posted example, 5 names are listed, but smith is listed twice.
Consequently, there are 4 unique names.

Does that help?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

"Anvil22" wrote in message
news I hace a list with multiple same names. in excel how can i count each
name
once for a get a list total . I tried most of the count function and
can
get
them to work.
smith
jones
smith
west
east
west
total 5 names.

Thank you

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post Anurag Excel Worksheet Functions 3 November 8th 07 08:46 PM Proton Excel Discussion (Misc queries) 7 June 13th 07 10:22 PM PGiessler Excel Worksheet Functions 6 June 24th 05 03:49 PM [email protected] Excel Discussion (Misc queries) 3 February 1st 05 10:23 PM Nodak Excel Worksheet Functions 1 January 25th 05 11:15 PM

All times are GMT +1. The time now is 07:16 AM.