Forum Discussion
Solved
Excel - List of Unique Names?
Hi, I'm a relative novice with Excel. I have a column of hundreds of names - many of which are duplicated. Both the first name and surname are in the same cell in one long column. Is ther...
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 would go with SergeiBaklan's formula because I can. The hard-wired range C:C could be replaced by a 2D range (or even 3D range across sheets) and it still works. It will ignore both blank cells and errors.
The catch is that Excel 2019 was already obsolete when it was first issued since dynamic arrays, which came out in 2018, didn't make the cut.