CODEWITHSUNDEEP
excelexcel-formula
kw1jybo
kw1jybo

Reputation: 152

Using SUMPRODUCT on column found by lookup

I have a sheet which has the following properties where Column A is item name, and Column B is item build price (computed).

enter image description here

I have second sheet which has the build information for the items, which has 1st column is the components, 2nd is the price per component, and each column after is the quantity of components needed to make item which is named in row 1

enter image description here

What I am wanting to do is to in sheet 1 have the calculated cost to make in column C. I tried doing various sumproducts formulas, like 

=SUMPRODUCT((BlueprintInfo!B:B)*(BlueprintInfo!C:BB)*(BlueprintInfo!C1:BB1=A3))

but it either doesn't work OR I get excel ran out of resources. Any ideas how to make this work?

thanks

Upvotes: 0

Views: 335

Answers (1)

BigBen
BigBen

Reputation: 49998

You could use an INDEX/MATCH within your SUMPRODUCT. Adjust the ranges as needed. 

=SUMPRODUCT(BlueprintInfo!$B$3:$B$6,INDEX(BlueprintInfo!$C$3:$E$6,,MATCH(A2,BlueprintInfo!$C$1:$E$1,0)))
  • The MATCH matches the Item Name in A2 within the range BlueprintInfo!$C$1:$E$1, returning 1 in this case.
  • Then use this as the column reference: in this case effectively INDEX(BlueprintInfo!$C$3:$E$6,,1), which is just BlueprintInfo!$C$3:$C$6.

This is the same as 

=SUMPRODUCT(BlueprintInfo!$B$3:$B$6,BlueprintInfo!$C$3:$C$6)

which is what you'd use if you hard-coded Column C for Item A.

enter image description here

Upvotes: 1

Related Questions