Counting names in a column but counting duplicate names once

[Guest]

I have a column with people who contacted us during a given month. Some
times the same person 2 to 3 time during the month. What funtion would I use
to count this list but only count duplicate names to = 1. Thank you.

1 Jim
2 stan
3 stan
4 bob
5 Jim
6 Scott

t = 4

 

[JulieD]

Hi

you can use the following array function (enter it with control & shift &
enter, not just enter):
=SUM(1/COUNTIF(A1:A10,A1:A10))

where your data is in the range A1:A10

check out http://www.cpearson.com/excel/duplicat.htm for more options and
details

 

[Guest]

Thanks much for your help Julie. Really appreceite it. I keep coming up
with 0 as the total. There's probably about 15 different names. I'll work
with that formula awile to see if it will solve this. I'm sure it's just me.
Thanks again.

Terry

 

[Domenic]

Try the following...

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

....confirmed with ENTER only, or...

=SUM(IF(A1:A10<>"",1/COUNTIF(A1:A10,A1:A10)))

....confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

 

[JulieD]

Hi

are you sure you're using control & shift & enter to enter the formula and
not just enter
have you changed a1:a10 to your actual range?

 

[Guest]

This is what I did Julie....
Copied and pasted the formula into the formula bar and changed the cell
coordinates
then hit control, shift, enter. Thanks again.

 

[JulieD]

Hi

i don't have any other ideas, do you want to email your workbook so i can
have a look? - send it direct to julied_ng at hcts dot net dot au - it's
11.38pm here so i probably won't get to it until tomorrow.

 

[Guest]

Thanks much Domenic...that did it!

Try the following...

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

....confirmed with ENTER only, or...

=SUM(IF(A1:A10<>"",1/COUNTIF(A1:A10,A1:A10)))

....confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

 

[Guest]

Thanks for you help and offer Julie. I guess it didn't like the copy/paste
method.
I entered it manually. Thanks again!

 

[JulieD]

Hi TBoe

glad it's solved