Sum of values from rows if criteria found in horizontal range

Solution 1:

Try this formula in B19 copied down

=SUMPRODUCT(SUMIF(B$2:B$11,INDEX(B$14:C$16,MATCH(A19,A$14:A$16,0),0),C$2:C$11))

MATCH gives you the correct row in table two
then INDEX gives you all countries in that row.
The INDEX/MATCH part then forms the criteria argument for SUMIF, and because the criteria is a range of values SUMIF returns an array (the sum for each country separately)......so SUMPRODUCT is used to sum the array, avoiding "array entry"

Solution 2:

Okay, since there's more than 1 country per region, you'll need a rather lengthy formula. I'm suggesting using both SUMIF() and VLOOKUP().

=SUMIF($B$2:$B$11,VLOOKUP(A19,$A$14:$C$16,2,FALSE),$C$2:$C$11)+
 SUMIF($B$2:$B$11,VLOOKUP(A19,$A$14:$C$16,3,FALSE),$C$2:$C$11)

The first SUMIF() is calculating the sum for Canada only, and the second for US only. Adding the two gives the sum for the region.

VLOOKUP() retrieves the country name from the region name, you'll notice the different is in the VLOOKUP(), where the first is 2 and the second is 3 (just before false). You can of course replace FALSE by 0 to output the same results.

Also, if you want a shorter, but an array formula, this one works as well:

=SUM(SUMIF($B$2:$B$11,IF(A19=$A$14:$A$16,$B$14:$C$16),$C$2:$C$11))

Since this is an array formula however, you'll have to use Ctrl+Shift+Enter

Solution 3:

You can try this:

=SUMPRODUCT($C$2:$C$11,--(IFERROR(MATCH($B$2:$B$11,$B14:$C14,0),0)<>0))

at cell B19 and drag it down. If you have more than 2 countries per region, you need to extend the $B14:$C14 accordingly.

Note: this is an array formula, so you copy it to the cell and press Ctrl+Shift+Enter to make it work.

IFERROR(MATCH(...,0)) returns an array whose elements show the position of each entry of the first table, in the appropriate row of the second table.
The logical condition converts all zeros to FALSE and all other numbers to TRUE
The -- part converts the TRUE and FALSE values to 1 and 0 respectively
SUMPRODUCT operates on two arrays: the first is the original range, and the second is a 0/1 array that encompasses the conditions we entered before.