CODEWITHSUNDEEP
google-sheets
Ryan Grush
Ryan Grush

Reputation: 2138

Google Sheets - dynamic lookup sum

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.

Upvotes: 1

Views: 103

Answers (2)

Tom Sharpe
Tom Sharpe

Reputation: 34420

=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

Upvotes: 1

user4039065
user4039065

Reputation:

This seems to be a straightforward SUMPRODUCT formula.

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

enter image description here

Sample Google-Sheet

Upvotes: 2

Related Questions