Formula to retrieve the the most frequent word in a list



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?


  • Nick Korna
    Nick Korna ✭✭✭✭✭✭

    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 ✭✭

    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!