Google Sheets Bucket Monthly Country Data (QUERY?)-CodePudding

I have a Google Sheets spreadsheet of monthly data showing percentages by country. There are a few countries and EU that we are trying to highlight, along with grouping the rest of the countries as "Rest of World". So, in effect, I need to bucket/sum the monthly percentages for EU countries, keep a couple other countries individually listed (same for all months), and bucket/sum any other countries with data for that month as "Rest of World" (could be different each month).

Here's a sample of the raw data:

Month   Country Percentage
2010-01 Sweden              18
2010-01 Republic of Korea   9
2010-01 Lebanon             4
2010-01 Malta               1
2010-01 Tuvalu              1
2010-02 Republic of Korea   24
2010-02 Lebanon             12
2010-02 Sweden              8
2010-02 New Zealand         1
2010-02 South Africa        0

Here's what I'm trying to output:

Month   Country Percentage
2010-01 EU                 19
2010-01 Republic of Korea   9
2010-01 Lebanon             4
2010-01 Rest of World       1
2010-02 EU                  8
2010-02 Republic of Korea  24
2010-02 Lebanon            12
2010-02 Rest of World       1

I've tried a variety of QUERY based attempts, but I've so far come up short. Would be grateful for any thoughts (including if this is not possible in Google Sheets). Thanks!

CodePudding user response：

try:

=ARRAYFORMULA(QUERY({A2:C, IFNA(VLOOKUP(B2:B, 
 QUERY(SPLIT(FLATTEN(IF(I2:J="",,I2:J&""&I1:J1)), ""), 
 "where Col2 is not null", ), 2, 0), B2:B)}, 
 "select Col1,Col4,sum(Col3) where Col3 is not null group by Col1,Col4 
  order by Col1,sum(Col3) desc label sum(Col3)''"))

CodePudding user response：

You can create a lookup table and use VLOOKUP to lookup each country. Then use QUERY to sum up the percentages in each group like this.