Sum Total Costs for Each Month Based between the Phase Dates Formula Needed

The purposes of this spreadsheet (see attachment) is to measure the project costs which is made up of two elements as seen in the Project By Month section, Actual Cost which is your real cost that has already occurred and Forecast which is the cost prediction for future months. The working formula I have is for the Project By Year, under 2017 and 2018, in cells B12 and C12. What these formulas do are sum the Actual Cost and Forecast for each month, but also takes account of the Project Reporting Month (May-17) in cell B3. So this formula is instructed to sum up all the past Actual Cost up to the Project Reporting Month (May-17) then after that month only sums up the future Forecast columns (the correct added columns I have highlighted in green).

So the new formula I need is to for the Project By Phase section. This too would also utilize the same formula that resides in the Project By Year cells, B12 or C12. However the added feature I need is the ability to correctly slice up these Actual Cost and Forecast sums that are set between each the Phase dates. I have put a simple addition formula into each of these cells to only provide the correct answer as a guide, but I need these simple addition formulas replaced by the true correct formula that I need your help in figuring out.

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

Garrett

Hi MarvinP,

Well your getting close. The only thing that is in your pivot table with consideration of the in the Project Reporting Month being May-17, the month of Jun-17 needs to then stop summing up the Actual Cost Columns and instead switch to summing up the Forecast columns. I also cant use a pivot table, I need a formula to control the monthly sum between the dates contained in the Project By Phase section, as this example is a just a small part of a much larger project cash flow that I;m putting together.

I hope this helps...

Garrett

Hi FlameRetired,

Sorry about the wrong dates with each month having the 17th day in them, so yes it was a typo on my part.

I believe I corrected them as they are only suppose to be the month and year abbreviated.

Let me know if I can provide you with any additional information.

Thank you for all your help!

Garrett

Sorry, here's the attachment with the dates now corrected.

Garrett

Hi FlameRetired,

Absoutly Yes!

See the new attachment as I have already made the change for you Boss!

Thank you for all your help!

Garrett

Hi FlameRetired,

For some reason the solution attachment that you have in last email isn't coming up and is causing an error on the web-sight.

It's just a glitch.

Can you reattach your solution spreadsheet and send it back to me?

Garrett

Hi FlameRetired,

Never mind about reposing the solution attachment.

I was able to plug your formulas into the spreadsheet and got it working perfectly!

I have attached the working solution spreadsheet below.

Thank you for all your help on this difficult and confusing request.

Garrett