Count the amount

[bjurney]

I have a list of around 500 names but they are not all diffrent. I am trying to figure out a formula to determine how many diffrent names are in this list.

For example:

Tom
Tom
David
Eric
Tom

would return 3.

I assume it might be a sumproduct formula, but I am not so good with the array formulas so not sure how to do it.
Thanks for the help

 

[Trevor G]

What about a CountIF formula and then perhaps use find duplicates in the Filter options and copy to separate sheet

 

[schielrn]

Try this:

=SUM(IF(FREQUENCY(IF(LEN(A1:A500)>0,MATCH(A1:A500,A1:A500,0),""), IF(LEN(A1:A500)>0,MATCH(A1:A500,A1:A500,0),""))>0,1))

This needs to be confirmed with control+shift+enter and not jsut enter.

Hope that helps.

 

[bjurney]

Thanks Schielrn, that is exactly what I needed, never would have figured that out on my own!

 

[Aladin Akyurek]

I have a list of around 500 names but they are not all diffrent. I am trying to figure out a formula to determine how many diffrent names are in this list.

For example:

Tom
Tom
David
Eric
Tom

would return 3.

I assume it might be a sumproduct formula, but I am not so good with the array formulas so not sure how to do it.
Thanks for the help

Less expensive, that is, faster...

Control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(A2:A6<>"",MATCH("~"&A2:A6,A2:A6&"",0)),ROW(A2:A6)-ROW(A2)+1),1))