Formula to retrieve the the most frequent word in a list

Options

Hey,

So let's say I have the list of countries below and I want to retrieve the most frequent country.

In that case, is Greece.

Any suggestions on how this can be found with a formula?

Answers

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭
    Options

    Hi @Ilias,

    It is doable with a helper column and then using INDEX/MATCH:

    The formula for the HELPER COUNTIFS is:

    =COUNTIFS(Country:Country, Country@row)

    The formula for the most common Country (in the "Formula" column) is:

    =INDEX(Country:Country, MATCH(MAX([Helper COUNTIFS]:[Helper COUNTIFS]), [Helper COUNTIFS]:[Helper COUNTIFS]), 0)

    The slight downside with this is if you have multiple countries tied for the first place, the formula will only give you the result of the first one (whichever comes first in the country column).

    Hope this helps; if you've any questions etc. then just post!

  • Ilias
    Ilias ✭✭
    Options

    Hi @Nick Korna,

    Thanks for your solution and works fine.

    Another downside is if you have a list of 70 countries and more criteria to consider then it means you need to create the helper COUNTIFS 70 times.

    Would have been more convenient if the INDEX formula could be used without the COUNTIFS.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!