Attn Frank Kabel - Question

M

Mike

Frank,

You've always been able to assist me in the past.... do you care to
take a crack at this one?

I have a list that contains about 45 different employee names. Many of
the names are listed more than once and the number of names on the list
is constantly changing. I'd like to know if there is a formula that
will allow me to count the total number of names with each name being
counted only once? Example:

John
Bill
John
Mary
Joan
Bill
Leroy

I want to know how many employees are in the list, not counting the
duplicates. In the example above the count would be 5. Hope that's
clear.

Thanks so much for your help.

Mike
 
D

David McRitchie

Hi Mike,
The only way I know would be to total a helper column.
B1: =IF(COUNTIF(A$1:A1,A1)=1,1,0)
double-click on the fill handle if there is always an entry
in Column A in the used range.

You can look at Chip Pearson's pages on duplicates, if
you want more information.
http://www.cpearson.com/excel/topic.htm
 
B

Bernard Liengme

Hello Mike,
Its 8 PM in Germany right now so Frank might be at the beer hall so please
allow a Canadian to make a suggestion:
Use =SUM(1/COUNTIF(A1:A7,A1:A7)) but remember to use SHIFT+CTRl+Enter to
complete it as it is an array formula.
Change A1:A7 to suit your worksheet.

Best wishes
 
F

Frank Kabel

Bernard said:
Hello Mike,
Its 8 PM in Germany right now so Frank might be at the beer hall so
please allow a Canadian to make a suggestion:
Click to expand...

Hi Bernard
unfortunately still at the office :-(
Greetings to Canada (still some hours of work for you...)

Regards
Frank
 
M

Mike

Bernard,

You're right.... and I could use a beer myself about now. Thanks for the
feedback... I'll give it a try. Appreciate your help.

Mike
 
M

Mike

Thank you David.... I'll give it a try. Appreciate your kind help!

Mike

David said:
Hi Mike,
The only way I know would be to total a helper column.
B1: =IF(COUNTIF(A$1:A1,A1)=1,1,0)
double-click on the fill handle if there is always an entry
in Column A in the used range.

You can look at Chip Pearson's pages on duplicates, if
you want more information.
http://www.cpearson.com/excel/topic.htm
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

Mike said:
Frank,

You've always been able to assist me in the past.... do you care to
take a crack at this one?

I have a list that contains about 45 different employee names. Many of
the names are listed more than once and the number of names on the list
is constantly changing. I'd like to know if there is a formula that
will allow me to count the total number of names with each name being
counted only once? Example:

John
Bill
John
Mary
Joan
Bill
Leroy

I want to know how many employees are in the list, not counting the
duplicates. In the example above the count would be 5. Hope that's
clear.

Thanks so much for your help.

Mike
Click to expand...
Click to expand...
 
M

Mike

Frank,

Thanks so much! And have a beer on me. It's time to hit the road home or
to the pub and have a pint or two. Later....

Mike
 
Top