# How to calculate the amount of times a name appears in a list?

Options

Hi, I am trying to see which top 3 names appears in a list of 100 names. Is there a formula I can use to do this?

I know there is a =COUNTIF formula but I would have to put the "NAME" in the formula which I really don't want to do for every name.

I was hoping there is something like a duplicate word counting formula out there?

• ✭✭✭✭✭✭
Options

@mande you a helper row with the following formula

=RANKEQ([Name Column]@row, [Name Column]:[Name Column], 0)What this does is ranks the names.

If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.

• ✭✭✭✭✭✭
Options

Hey @Mandeep and @Mark.poole

RankEQ and RankAVG require numeric fields, which means you will need an additional helper column to first calculate the the count.

You can use the COUNTIFS([Name Column]:[Name Column],[Name Column]@row) to find that count. You could then use this helper column in the RankEQ or RankAvg function.

If you only want the top 3 counts, as an alternative, you could build 3 summary fields on your sheet for this. You would change the boldfaced number 1 to 2 or 3 for the respective 2 and 3 positions.

=INDEX(COLLECT([[Name Column]:[Name Column], [Helper CountIfs]:[Helper CountIfs], @cell = LARGE([Helper CountIfs]:[Helper CountIfs], 1)), 1)

Kelly

• Options

Does this way mean I have to reference each individual separately?

My sheet looks something like this :

( This is a report that gets done every week, so I would be comparing mulitple reports such as below

Name Option 1 Option 2 Option 3

John Active

George Active

Sam Active

• ✭✭✭✭✭✭
Options

Actual screenshots would help us

Are you saying your sheet could look like this?

Name

Option1

Option2

Option3

John

Active

Geoge

Active

Sam

Active

When you say you will be comparing different reports would do you mean?

Please @mention me to call me out

Kelly

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!