Data is all in the same workbook
Sheet ‘Cost Calc’ contains adjacent columns of (fitting) part numbers and their quantity. The part number appears multiple times in some but not all columns.
Part Number columns:
AK AO AS AW BA BI
Quantity columns:
AL AP AT AX BB BJ
Sheet ‘Fittings’ contains a comprehensive list of fittings in column A
This array formula in B2 works to total the quantity of parts in AK and their quantity in AL that match the part number in A2
=SUM(('COST CALC'!AK$8:AK$376=A2)*('COST CALC'!AL$8:AL$376))
What I want to do is total the quantity in all 6 part number columns,
I can get the answer I want with the following:
=SUM(('COST CALC'!AK$8:AK$376=A2)*('COST CALC'!AL$8:AL$376) + ('COST CALC'!AO$8:AO$376=A2)*('COST CALC'!AP$8:AP$376)) + etc. etc. etc. for the remaining columns )
But is there a way to do an array that looks at all the part number columns and their adjacent quantity and totals them up without spelling out all 6 pairs of columns as above?
Note there are non related columns in between the pairs of part numbers & quantity columns.
PS. when I enter this SUM formula by clicking on the cost calc sheet cells (rather than typing the entire formula) it defaults to the fittings sheet name and I have to manually erase fittings! and type ‘cost calc’!
Why is this?
Bookmarks