How many times a name appears.

[Dee1234910]

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! 

[#2]

@Dee1234910 

=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.

[#3]

@Dee1234910 

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

[#4]

@Dee1234910 

=HSTACK(UNIQUE(A1:A8),BYROW(UNIQUE(A1:A8),LAMBDA(x,COUNTIF(A1:A8,x))))

This is with Office 365 or Excel 2021.