SUM row with multiple VLOOKUP

Question

hope you can help me out!

I have a first sheet with the following information:

a 3
b 3
c 4
d 10

Then I have another sheet with the following information:

John a a c d
Joe  b d d c

I would like to display a sum of the values in front of those rows, considering the first sheet values. So it would display:

John a a c d 20
Joe  b d d c 27

Don't know how to do this exactly. would love your help. Thanks so much!

Answer 1

Try this array formula,

=SUM(SUMIFS(B$2:B$5, A$2:A$5, E2:H2))

Enter it with ctrl+shift+enter, not just enter.