Google Sheets - dynamic lookup sum

1
votes

I have a vertical list of points and several horizontal sheets of data. I want to get a sum of the data from each of those sheets, multiply them by value of their header key, and get a sum of each row of data.

enter image description here

For row 2 I want the total to be 1,011 (3 x 123 = 369, 2 x 321 = 642, 369 + 642 == 1,011) where it looks up the header of aaa and bbb, gets their point value, and multiplies it by their row value. I'm sure I can hack it and get the result but I'd like to get an efficient way to do it.

2
google-sheets

2 Answers

2
votes

This seems to be a straightforward SUMPRODUCT formula.

=sumproduct((A3:A4=D1:E1)*(B3:B4)*(D2:E2))

enter image description here

Sample Google-Sheet

1
votes
=SUMPRODUCT(B:B*iferror(hlookup(A:A,D1:E2,2,false),0))

or

=SUMPRODUCT(B:B*iferror(hlookup(A:A,1:2,2,false),0))

enter image description here