I have an Excel table with three columns. Column A has a list of countries, Column B has a list of cities in each country and Column C has populations of those cities. The way the table is structured makes it so that Column A will have repeated names of countries - as many times as the number of cities in each country, in column B.
I would like to sum the populations of the first five cities in each country.
I have tried using SUMIF and COUNTIF but haven't managed to do it. How can I sum the populations (in row C) of the first five cities appearing for each country?
Are you trying to sum the population of the first five cities in the list or the population of the top 5 most populous cities for each country (which if the list is sorted by population, these are the same)? If it's the latter you can do it with a one line array formula
=SUM(LARGE(IF(A:A="CountryName",C:C),{1,2,3,4,5}))
(Ctrl+Shift+Enter after setting up the formula)
Where you replace "CountryName" with a reference to the country you want the sum of the top 5 populations for. I think the only issue with this is it will fail if there are less than 5 cities in a country on the list.
Here is a version of the formula that works when there are less than 5 cities but still caps at out at the top 5. Getting an array of 1-n values is kind of an ugly hack in Excel but this seems to work.
=SUM(LARGE(IF(A:A="CountryName",C:C),ROW(OFFSET(A1,,,MIN(COUNTIF(A:A,"CountryName"),5)))))
(Ctrl+Shift+Enter after setting up the formula)