CODEWITHSUNDEEP
excelexcel-formula
robbie
robbie

Reputation: 647

Calculating a table of units with cost defined via vlookup

I have a spreadsheet that looks like this.enter image description here

In this example, I calculated the Totals with something by manually multiplying the unit counts (B2:E4) with the costs (B7:B10) of the appropriate product and summing each row.

My actual datais too complicated to do this manually. What I would like to do is have a formula to automatically generate the Total column. I imagine this is some magic combination of SUM, VLOOKUP & Array Formulas, but I've failed to find the solution.

Here is the sample in spreadsheet form if that helps.

Thanks.

Upvotes: 0

Views: 96

Answers (1)

Scott Craner
Scott Craner

Reputation: 152585

You can combine a lookup with SUMPRODUCT:

=SUMPRODUCT((LOOKUP($B$1:$E$1,$A$7:$A$10,$B$7:$B$10))*B2:E2)

enter image description here

If the cost were in row 2 a simple SUMPRODUCT would suffice:

=SUMPRODUCT($B$2:$E$2,B3:E3)

enter image description here

Upvotes: 2

Related Questions