Array formula?

[Colonel]

Worksheet A (Base Volume Data) contains a list of project numbers with a quantity per week for each project. Worksheet B contains activity data for component parts in these projects; Column B contains project numbers; Column N contains cycle times for a specific operation.

Obviously, retrieving the quantity per week via a vlookup and multiplying by the cycle time is easy; =(N4*VLOOKUP(B4,'BASE VOLUME DATA'!$B$4:$E$7,4,))

However, rather than have a separate column to perform this calculation for each component part and then doing a sum of the results, I'm wondering if there is a way to perform the calculations for each row from within one cell. Can this be done with an array formula?

 

[StephenCrump]

Welcome to the Forum!

Try:

=SUMPRODUCT(IFERROR(LOOKUP(B4:B100,'BASE VOLUME DATA'!B4:B100,'BASE VOLUME DATA'!E4:E100),0),N4:N100) array-entered, assuming 100 >= your last row

 

[Colonel]

Thank you - your formula does give me a result, but it's different to the result I get if I do it the long-winded way.

The BASE VOLUME DATA worksheet currently only contains 4 rows of data;



and the data in worksheet B is contained in rows 4:42, so the formula I have is;

{=SUMPRODUCT(IFERROR(LOOKUP(B4:B42,'BASE VOLUME DATA'!B4:B7,'BASE VOLUME DATA'!E4:E7),0),N4:N42)}

This returns a result of 32.96, but it should be 67.55

If I evaluate the formula, it looks like it's not looking at the right data in the lookup;



Column B in Worksheet B contains 17 entries for project 1236, 14 entries for project 1192, 4 for project 1289 and 4 for project 1165

 

[Peter_SSs]

Try this (not array-entered).

=SUMPRODUCT(SUMIF('BASE VOLUME DATA'!B4:B7,B4:B42,'BASE VOLUME DATA'!E4:E7),N4:N42)

 

[Colonel]

That gives me exactly the correct answer, thanks Peter

 

[Peter_SSs]

That gives me exactly the correct answer, thanks Peter

Thanks for letting us know. Glad to help.