Count unique text in a single column

[rlee1999]

Been searching through old posts for 45 minutes...apparently my need is too simple.

I have a list of names that occur over and over, say A1:A500. I am looking for a formula that will simply count how many unique names there are and return that number to a single cell. Any suggestions?

 

[Domenic]

Try the following formula, which needs to be confirmed with CONTROL+SHIFT+ENTER...

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

Hope this helps!

 

[rlee1999]

I receive a #VALUE! error. Do blank rows interfere?

 

[Domenic]

The formula ignores blank rows. Make sure that the formula is confirmed with CONTROL+SHIFT+ENTER, not just ENTER. If done correctly, Excel will automatically place curly braces {...} around the formula.

 

[GreasySpot]

Once you enter the formula you have to close it with the Ctrl+Shift+Enter keystrokes so it puts { } around your formula.

 

[rlee1999]

Ahhhh...that makes sense, and worked perfectly. Thank you!