I want this formula to add costs (from column Assumptions!H60:H649) in between a start date/month (Assumptions!J60:J649) and end date/month (Assumptions!L60:L649) that correspond to a certain cost category (the category can be picked in Assumptions!C60:C649, but they are added into a summary row with the category listed in Accruals!$B$85 for example). Monthly dates on the Accruals sheet are spread horizontally across D5:BK5 (60 months), so this formula will be dragged to the right (also down for some other categories).
The Mapping reference is to how often this will repeat. This mapping reference is for an "annual" repeat, I also have a "quarterly" repeat in this formula that just replaces the 12 with a 3. There's probably a more elegant way to do that part but this works.
My issue is that I know MONTH(Assumptions!$J$64) (bolded below) needs to reference J60:J649 so all the costs that should start in a particular month are included in this month's cell (Accruals!D85 in this example), but I'm not sure how to make that part of the formula work with that array.
=...+IFERROR(IF(MOD(MONTH(Accruals!D$5)-MONTH(Assumptions!$J$64),12)=0,INDEX(Assumptions!$H$60:$H$649,MATCH(1,IF(Accruals!D$5>=Assumptions!$J$60:$J$649,IF(Accruals!D$5<=Assumptions!$L$60:$L$649,1)),0)),0),0)*IFERROR((MATCH(Mapping!$H$6,Assumptions!$K$60:$K$649,0))/(MATCH(Mapping!$H$6,Assumptions!$K$60:$K$649,0)),0)
The Mapping reference is to how often this will repeat. This mapping reference is for an "annual" repeat, I also have a "quarterly" repeat in this formula that just replaces the 12 with a 3. There's probably a more elegant way to do that part but this works.
My issue is that I know MONTH(Assumptions!$J$64) (bolded below) needs to reference J60:J649 so all the costs that should start in a particular month are included in this month's cell (Accruals!D85 in this example), but I'm not sure how to make that part of the formula work with that array.
=...+IFERROR(IF(MOD(MONTH(Accruals!D$5)-MONTH(Assumptions!$J$64),12)=0,INDEX(Assumptions!$H$60:$H$649,MATCH(1,IF(Accruals!D$5>=Assumptions!$J$60:$J$649,IF(Accruals!D$5<=Assumptions!$L$60:$L$649,1)),0)),0),0)*IFERROR((MATCH(Mapping!$H$6,Assumptions!$K$60:$K$649,0))/(MATCH(Mapping!$H$6,Assumptions!$K$60:$K$649,0)),0)