I look at a forecast that tells me what demands are for a part I make. The demands are listed in weekly buckets. The forecast shows demands for this week and all future weeks of the year. The forecast also shows demands that are listed in prior weeks as past due.
I have a table broken down weekly that uses and index(match function which pulls the weekly demand from the forecast and places them in the appropriate weekly bucket.
I'm struggling to find a formula that will sum all the qty from past weeks and display them in one cell.
I think it going to be resemble something close to the code below. I know this code is incorrect.
=SUMIF(DataPSI!$B$6:$BR$98,WEEKNUM(DataPSI!$B$5:$AI$5,1)<WEEKNUM(TODAY(),1))
Is there a combination of functions I can do to achieve this?
Please see images below for a breakdown:
Cell Range: E10:AZ10 uses the following index(match to pull from a pivot table. As you can see in the image below the current week is week 6 (as indicated in cell E5 and cell A7):
Pulling from this pivot table. As you can see in the pivot table below, it is showing demand for week 6 and beyond BUT it is also showing demand for weeks 3 & 5.
I need a formula that will take the sum of those past due weeks and return the result to cell C12 in the first image.
Note: The pivot table in the second image displays different results as the week changes, you will notice the Date filter in cell B1. So next week when my power query uploads new data to the master table, the pivot table in the second image will add the new week to select.
So the formula that pulls past due demand needs to be able to recognize what the current week is and sum only what is prior to the current week. Example: Current week is 6, sum all weeks that have value in weeks 1-5. Fast forward to next week; Current week will be week 7, sum all weeks that have values for weeks 1-6.
Pivot Table Data Layout
Results Table Data Layout
