Sum values from multiple rows using vlookup or index/match functions

excel, excel-formula

Solution

=SUMPRODUCT((A1:A5="FRANCE")*B1:D5)

Problem

I want to sum the values in columns B, C, and D using a formula. I tried combining `SUMIF`, `VLOOKUP` and `INDEX` with `MATCH` but no luck. I would like to look for France and then add the values in B, C and D. I have tried: ``` =SUM(VLOOKUP(A9,A1:D5,{2,3,4,},FALSE)) ``` But it returns the total value from the first row and does not include the 3rd row: ``` A B C D 1 FRANCE 152 252 354 2 ITALY 255 184 328 3 FRANCE 221 215 114 4 SPAIN 215 255 356 5 USA 125 222 584 6 7 8 TOTAL PER COUNTRY 9 FRANCE 10 SPAIN ```

Original source