Google Sheets Bucket Monthly Country Data (QUERY?)

Question

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!

Answer 1

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)''"))