Formula to Sum Specified Cells Decided by Multiple Dates and Titles

I have two separate formula's under the Project Year's section (Cells B8 and C8) that are incomplete and I cannot figure out the last part of the equation I need to make it fully work.

What I do have working is based on the Reporting Month of Mar-19 (Cell: C2), it will scan across the lower Project Month's section for the month and year (Cells: B11:Y11), whether it's listed as a Forecast or Actual Cost (Cells: B12:Y12), and lastly sums up the correct dollar amounts below (Cells: B13:Y13).

So as shown in the example above, the formula under the Project Year's section in 2019 has a total of $37,925 (Cell: B8). The current formula takes in account of the Reporting Month of Mar-19 (Cell: C2), then it will sum all the dollar amounts on that month (Mar-19) and past month's (so in total, Jan-19 thru. Mar-19) that only have the title of Actual Cost, then it will sum all the dollar amounts in the very next month (Apr-19) and all future month's (so in total, Apr-19 thru. Dec-19) that only have the title of Forecast. In the final total in the Project Year's, 2019 total is $37,925.

The problem is that in the Project Month's section, the start date may not always begin with the first month of the year. So, with my current fixed formula that is only set between the 12 months of 2019, then another second formula set between the 12 months of 2020. If the starting date in the Project Month's section is set to begin on, let’s say Feb-19 (see dropdown dates in cell C4 to change the example), then my fixed formula of 12 months starts on Feb-19 and unintentionally is pushed out by the fixed formula 12 month duration to the first month of 2020 (Jan-20) which in the end provides the wrong totals for both 2019 at $37,425 (Cell: B8) and 2020 (Cell: C8) Project Year's totals (in this example, the 2020 dollar amount does not change due to empty cells from AX13 and beyond).

I can't seem to figure out the correct formula that I need to utilize the Project Year's date of 2019 and have the Project Month's formula section stretched across the entire span of dates (Cells: B11:AW11) only to include the months in 2019 and exclude the months in 2020, weather it's listed as a Forecast or Actual Cost, and lastly sums up the correct dollar amounts. So, it will now be one standard formula that will work in both of the Project Year's section (Cells: B8 and C8).

Thank you for your help and please let me know if you need any additional information.

Garrett

In the list in sheet Month Drop down list, the starting date is 19/01/2018, I change it to 31/01/2018
Next cell is EOMONTH($B3,1)
Drag down
Sheet Example, C2 & C4 now is last date of month
In B8:

Please Login or Register  to view this content.

Drag to C8

Example

B8=SUMPRODUCT(($B$11:$AW$11>=$C$4)*(($B$11:$AW$11<=$C$2)*(YEAR($B$11:$AW$11)=B$7)*($B$13:$AW$13)))

copy across until C8

Please provide a fresh version of the workbook showing the issues you are having with the formula suggested AND your manually calculated expected results.

Please don’t use private messages to prompt members to help with your queries. I am not available to look at your issues now as I am about to go offline. Maybe somebody else will.

The answers in post #8 is the same as in the worksheet.

I suggest you create a scenario in the workbook where the formulas produce the wrong results and then manually type in the correct results.