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


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?


  • Mark.poole
    Mark.poole ✭✭✭✭✭✭

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

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭

    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)


  • Mandeep

    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

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭


    Actual screenshots would help us

    Are you saying your sheet could look like this?











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

    Please @mention me to call me out


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!