Formula to compute costs based on months to only include months between the EndDate and GoLiveDate

dsmartsheetuser

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!

Julio S.

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 cross-sheet 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