Question

[aroraaj]

I have two excel sheet. In one of the excel sheet I have two columns one for part name and the other for Single Source. My second sheet has 9 columns. It has Part Name, Gold Supplier, Gold Point, Capacity Ranking, Capacity Ranking Data, Scorecard, Scorecard Data, Quality Card, Quality Card Data.

Basically I have to add a formula in my first sheet and if the second column has Single Source listed I don't have to do anything but if its blank I need to calculate Gold Points + Capacity Ranking Data + Scorecard Data + Quality Card Data.

Is there a way to do that...I was thinking of doing VLOOKUP by matching the part name but I'm not sure how that'll work by adding the data.

Thanks

 

[Andrew Poulsom]

This will sum every other column in B:I for the row that contains the Part Name in column A:

=IF(B2="","",SUMPRODUCT(INDEX(Sheet2!$B$2:$I$3,MATCH(A2,Sheet2!A$2:A$3,FALSE),0),--(MOD(COLUMN(INDEX(Sheet2!$B$2:$I$3,MATCH(A2,Sheet2!A$2:A$3,FALSE),0)),2)=1)))

Adjust the sheet and range references to suit.