Forum Discussion
Excel - List of Unique Names?
Does this formula work for you? It doesn't use functions that are only available in recent versions of Excel.
=SUMPRODUCT((C4:C641<>"")/COUNTIF(C4:C641,""&C4:C641))
or (confirmed by pressing Control+Shift+Return or Command+Return):
=SUM(IF(FREQUENCY(IF(C4:C641<>"",MATCH(C4:C641,C4:C641,0)),ROW(C4:C641)-ROW(C4)+1),1))
I'll try your suggestion. The Excel version I'm using is 2019 for Mac. Some of the cells I've listed (C4:C641) are blanks - is this a factor? If so, any way of excluding them?
Thanks, Pete
Does this formula work for you? It doesn't use functions that are only available in recent versions of Excel.
=SUMPRODUCT((C4:C641<>"")/COUNTIF(C4:C641,""&C4:C641))
or (confirmed by pressing Control+Shift+Return or Command+Return):
=SUM(IF(FREQUENCY(IF(C4:C641<>"",MATCH(C4:C641,C4:C641,0)),ROW(C4:C641)-ROW(C4)+1),1))
- Hi Hans,
Thanks very much for your reply and suggestions.
I've used the first version you've supplied and it has returned a figure of 417 unique names. By adding "-1" to this formula to remove the blank cell I've arrived at the correct number required.
Thank you very much for taking the time to produce this, it's very much appreciated.
Kind Regards
PeteThe formula already excludes blank cells from the count!
But if you have cells that only contain a space (or multiple spaces), those would be included.