0

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?

franciscofcosta
  • 833
  • 5
  • 25
  • 53

2 Answers2

2

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)
Wedge
  • 1,766
  • 1
  • 8
  • 14
  • Thank you! Worked perfectly fine since the table was sorted by population. – franciscofcosta Aug 16 '17 at 21:50
  • Any idea on how to resolve the issue of those countries where there are less than 5 cities? – franciscofcosta Aug 16 '17 at 21:50
  • Sure, I added a version of the formula that dynamically creates the array of numbers to lookup by counting the number of cities and capping it to 5. Kind of an ugly hack thing to do in Excel but I think it works. – Wedge Aug 16 '17 at 23:34
0

Add a column D. In D2 write the following formula D2=COUNTIF($A$1:$A2,$A2) and drag it down. Now what this will do is ranking the instances of a particular country. Now it's a very simple formula for column E, where you will get the sum E2=SUMIFS($C$2:$C$1000,$A$2:$A$1000,$A2,$D$2:$D$1000,"<=5") and drag it down Now for each country you will have the sum of population of first 5 cities

harinp715
  • 41
  • 2