Formula to compute costs based on months to only include months between the EndDate and GoLiveDate
I have a sheet to compute cost based on EndDate and GoLiveDate. If the StartDate is less than GoLiveDate then cost is sum of monthly cost between StartDate and GoLiveDate.
Project Name:
Test 3  StartDate: 5/29/2022  GoLiveDate: 9/7/2022, Cost: May cost + June cost + July cost + August cost + September cost.
Test 4  StartDate: 6/19/2022  GoLiveDate: 10/21/2022, Cost: June cost + July cost + August cost + September cost + October cost
Currently I have the formula: =(IF(EndDate@row < GoLiveDate@row, SUM(March@row:June@row), 0)). However, I have to manually change the SUM formula to capture the specific months.
How can I automate this. Please find attached screenshot of my test sheet for reference.
Thank you! Appreciate all the help!
Answers

Hi @dsmartsheetuser,
In order to achieve the calculations that you intend, I'd suggest replacing all columns names as months for just two columns "Cost" and "Cost by Month" as shown below. If you wish to keep your sheet clearer you may hide these columns or put them in a separate sheet and use crosssheet references in your formula to perform these calculations.
With this setup, the following formula should achieve what you intend:
=IF(EndDate@row < GoLiveDate@row, SUMIFS([Cost by month]:[Cost by month], Month:Month, >=MONTH(EndDate@row), Month:Month, <=MONTH(GoLiveDate@row)), 0)
I hope this can be of help.
Cheers!
Julio
Help Article Resources
Categories
Check out the Formula Handbook template!