List of Names Multiple Times - Outputting # of Appearances

**imsteve123** · 10-06-2017, 03:40 PM

Hey guys,

So I have a list of names spread across my excel file, let's say for example:

	A	B	C
1	John S	John D	Amy L
2	Eric G
3	John S	Amy L

What I'm trying to do is take A1:A3 - C1:C3 and output this list (order doesn't matter):

Name	# of Appearances
John S	2
John D	1
Amy L	2
Eric G	1

I know how to do a CountIF function by just writing out each name but the list I have is huge with over hundreds of names. I'm wondering if there's a function that can do a lookup on a unique name and list it out only once without skipping any cells. Then I can just do a CountIf of that cell value which be the name of that person but I'm trying to avoid duplicates.

Let me know if that makes sense - happy to clarify and help would be greatly appreciated.

Thanks!

**tim201110** · 10-06-2017, 04:07 PM

=IFERROR(INDIRECT(TEXT(SMALL(IF((_r<>"")*(COUNTIF($A$1:A1,_r)=0),ROW(_r)*10000+COLUMN(_r)),ROWS($A$1:A1)-COUNTA($A$1:A1)),"R000000C0000"),),"") as array formula