Forum Discussion
How many times a name appears.
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:
John
John
John
John
Sally
Sally
Bob
In say column G+H it says
John 4
Sally 2
Bob 1
Appreciate any help on this!
=COUNTIF($A$1:$A$7,A1)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.
If you work with Excel 2021 or Excel for the web or Office 365 you apply HSTACK, UNIQUE and LAMBDA.
=HSTACK(UNIQUE(A1:A8),BYROW(UNIQUE(A1:A8),LAMBDA(x,COUNTIF(A1:A8,x))))
This is with Office 365 or Excel 2021.
