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?

Answers

  • Mark.poole
    Mark.poole ✭✭✭✭✭✭
    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.

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    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

  • Mandeep
    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

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Options

    @Mandeep

    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!