sumproduct on a variable range

[Mike Proffit]

Hi All.

A1:A5 lists the number of times a part is used in an assembly
B1:B5 lists the number of each assembly I want to build
B6 = SUMPRODUCT(A1:A5,B1:B5), which successfully gives me the total demand
for the part.

Now I want to expand the build range to be based on the lead time of the
part. So now A6 = 5, representing 5 weeks. I want a formula in B6 that sums
each of columns B, C, D, E, F * A1:A5.

trying B6 = {SUMPRODUCT(A1:A5,B1:F5)} as an array fomula or not yeilds
#VALUE error since (I think) the range shapes are different.

Ultimately, I want the sum of A1:A5 * each of the columns offset to the
right up to the lead time value in A6. In this case, offset 0, 1, 2, 3, 4,
5.

I'm stumped!

Thanksomuch,
Mike Proffit

 

[Trevor Shuttleworth]

Try:

=SUMPRODUCT(A1:A5,B1:B5+C1:C5+D15+E1:E5)

Regards

Trevor

 

[Mike Proffit]

That works if arrays 2 - n of the sumproduct function are known. Problem
here is that there are varying numbers of columns to include in the formula,
determined by the lead time value in A6.

I'm thinking a custom function is needed. I'll post in that group.

But Thanks!
MikeP