Formula to retrieve the the most frequent word in a list

Ilias

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?

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

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.