I have a list of countries and company types (A, B, C and D) in a Google Sheet. For each country, there are multiple entries, one entry per company. Countries are in column A of the spreadsheet, the type of the companies is in column B.
I would like to find out, for each country, what is the most common type of company. In other words, I would like to know, for each country, what is the letter in column B, that appears the most.
I have managed to find out what is the letter that appears the most in the entire list of countries. To do that, I have used this formula in cell D2:
=ARRAYFORMULA(INDEX(B1:B,MATCH(MAX(COUNTIF(B1:B,B1:B)),COUNTIF(B1:B,B1:B),0)))
It gives me the most common letter overall. However, I am not sure how to include a condition in the formula that will give me the answer considering one particular country only.
I have created a sample sheet, which you can find here. I have created a table on D1:E16 in which I would like to have the formula.
Thank you.
This should do it:
=ARRAYFORMULA(INDEX($B$1:$B,MATCH(MAX(COUNTIFS($B$1:$B,$B$1:$B,$A$1:$A,D2)),COUNTIFS($B$1:$B,$B$1:$B,$A$1:$A,D2),0)))