Counting Unique Names

[Karen]

I have a list of technician names in the cell range F1:F100. There are many
duplicates of names within these cells. I want to know how many unique names
appear in this range. For example: Let's say that Tom appears 20 times, Joe
50 times, Bill 20 and Sue 10 times. I need to know that only 4 technicians
were involved. What formula would I use for this?
ANY help would be greatly appreciated
Thank you, Karen

 

[Mike H]

Karen

For this ARRAY formula to work there must be no blanks in the rrange

=SUM(IF(FREQUENCY(MATCH(F1:F100,F1:F100,0),MATCH(F1:F100,F1:F100,0))>0,1))

This is an array formula which must be entered by pressing CTRL+Shift+Enter
'and not just Enter. If you do it correctly then Excel will put curly brackets
'around the formula {}. You can't type these yourself. If you edit the formula
'you must enter it again with CTRL+Shift+Enter.

Mike

 

[NBVC]

Try:

=SUMPRODUCT((F1:F100<>"")/COUNTIF(F1:F100,F1:F100&"")

--
NBV

Where there is a will there are many ways.

'The Code Cage' (http://www.thecodecage.com

 

[Karen]

Thank you so much for your help.
Karen

 

[Karen]

Thank you so much for your help.
Karen

Karen

For this ARRAY formula to work there must be no blanks in the rrange

=SUM(IF(FREQUENCY(MATCH(F1:F100,F1:F100,0),MATCH(F1:F100,F1:F100,0))>0,1))

This is an array formula which must be entered by pressing CTRL+Shift+Enter
'and not just Enter. If you do it correctly then Excel will put curly brackets
'around the formula {}. You can't type these yourself. If you edit the formula
'you must enter it again with CTRL+Shift+Enter.

Mike