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))
One of these perhaps.
List = your range
'Count after dupes are removed.
=COUNTA(UNIQUE(list))
'Count of names only appearing once in list.
=COUNTA(UNIQUE(list,,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.
- Are all your names in 1 cell?
- Hi Patrick
The complete names of each person are in their own cells - so 638 cells in total. However, some of those cells have no name in. Is that a factor?
Thanks, pPete
