# How many times a name appears.

Copper Contributor

# 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!

3 Replies

# Re: How many times a name appears.

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

# Re: How many times a name appears.

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

# Re: How many times a name appears.

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

This is with Office 365 or Excel 2021.