Excel - What function to achieve this task

Question

I have a forecast in row 2. The item has to be growing for a total of 5 weeks, as denoted by Column B. This time can vary.
As a result the product takes up space for 5 weeks, as shown by rows 3 to 7. I have them summed the total space taken in Row 8.

Does anyone have a suggestion of how I could automatically go from the Forecast line in Row 2, to the Total line in Row 8 please? To save the manual work of adding in each line, then adding them up.

Even a suggestion of an excel function would be much appreciated. I have tried and my lack of knowledge isn't helping. Thanks

Answer 1

Try this solution that use the INDRECT function to get a dynamic array of the product quantities that need space in a given week.

In C3: =LET(startWeek,MATCH(C1,$C$1:$R$1,0)+3,endWeek,startWeek+$B$2-1,range,INDIRECT("R2C"&startWeek&":"&"R2"&"C"&endWeek,FALSE),TRANSPOSE(range))

Copy formula in C3 across to D3 through R3.

In C13: SUM(C3#)

Copy formula in C13 across to D13 through R13.

This would give you something that looks like this:

You can of course combine the formulas in C3 and C13 and just get one row with the total for each week:

In C3: =LET(startWeek,MATCH(C1,$C$1:$R$1,0)+3,endWeek,startWeek+$B$2-1,range,INDIRECT("R2C"&startWeek&":"&"R2"&"C"&endWeek,FALSE),SUM(range))

Again, copy this from C3 across to D3 through R3.