Counting the countries

[Kate.mills]

Hi,

I wonder if you could help, I have a list of countries and need to find out which country appears the most I have been using

=ArrayFormula(INDEX(Regions!J$5:J$45,MATCH(Max(Countif(J5:J45,J5:J45)), COUNTIF(J5:J45,J5:J45),0))&"("&max(countif(J5:J45,J5:J45))&" times)")

from one of your other threads. Which is great but in this column I have 3 instances of one country and also 3 instances of another country. I need to have the result to be both countries and not the one that appears first in the list.

Your help would be greatly appreciated.

Many thanks,
Kate

 

[Chihiro]

Without sample file, it's difficult to help you. As well, that formula looks like Google Sheet formula.

In most cases, I'd recommend using PivotTable to summarize data as you've mentioned, rather than using formula.

 

[Kate.mills]

Hi Chihiro,

I am using google sheets and also Excel I have attached an example of the query. In the actual workbook the information is being correlated on a separate sheet in a specific layout in quarters.

Thank you
Kate

 

[NARAYANK991]

Hi ,

See if this is OK.

Narayan