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))
Your first example is applicable to what I want but upon use it only gives a value of 1 instead of the total number of unique names. Anything else needed.
That is odd. The danger with the formula is typically one of giving 'false positives' where trivial differences give rise to distinct names. To get 1 it is likely that the COUNTA is hiding a problem with FILTER. It would be worth seeing what you get from
= UNIQUE(list)before performing the count. My best guess is that you are using an obsolete version of Excel that does not have the UNIQUE function.
- Hi Peter,
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, PeteIn general that could be
=ROWS( UNIQUE( TOCOL(C:C,3) ) )if only it's in the same column C.
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
Pete
