Excel : Summation of data that belongs to certain id

Question

It is easier to illustrate the problem based on the following example:

I have two sheets: mapping and data.

1. The Mapping sheet contains a (one-to-many) mapping table between a country name and the corresponding Group ID:
   

2. This table (from the Data sheet) contains the country name with the corresponding GDP:
   

The objective is to create a summary table that maps the Group ID to the sum of GDP (for all countries and all years) in a separate sheet:

    

I tried to use the SUM and SUMIFS functions as recommended at Exceljet.  They propose the following formula based on their own example.

=SUM(SUMIFS(sum_range,criteria_range,{"blue,"red"}))

However, the challenge arises due to two reasons:

1. I cannot list down the ID in an array form as I have many ID variations.  Also, the actual ID consists of both numbers and alphabets.
2. I must follow a guideline not to merge the tables into a single sheet.

Answer 1

If you’re willing and able to add a helper column to your Data sheet, set Data!F12 to

=INDEX(Mapping!C$4:C$8, MATCH(C13,Mapping!D$4:D$8,0))

and then, on your Summary sheet, set B3 to

=SUMIFS(Data!$E$13:$E$22, Data!$F$13:$F$22, $A3, Data!$D$13:$D$22, B$2)

and drag/fill down and to the right.