How many times a name appears.

Copper Contributor

Hi all, 


We often need to see how many times a name appears on some data (pulled at regular intervals) is there a formula that will create a list of the name that appear in column A. and how many times their name is mentioned in that column.


I could have a list of potential names in one column (if needed column G)- and then each time the name is mentioned it creates a tally (number is fine).


So it might say:








In say column G+H it says

John 4

Sally 2

Bob 1


Appreciate any help on this! 

3 Replies



With e.g. Excel 2013 you can apply this formula. Then copy range A1:B7 and paste only values in columns G and H. Then you can select Data -> Remove duplicates

to return the result in the screenshot.

count names.JPGIf you work with Excel 2021 or Excel for the web or Office 365 you apply HSTACK, UNIQUE and LAMBDA.


One more variant is PivotTable filtered on blank labels, but it requires Refresh.





This is with Office 365 or Excel 2021.

hoiw many times a name appears.JPG