Sum values across multiple months & years based on start and end dates

[wardfxj1]

Hi there
I'm really struggling to find the correct combo of formula to answer the following:

I have start and end dates of projects, Total annual values (2018-2020) and depending on when the project starts and ends profile the value across the months of the project ( some projects last multiple years)

Example below (although note example doesn't show all of the months to profile)

[TABLE="width: 883"]
<colgroup><col><col><col span="3"><col><col><col span="6"></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2018[/TD]
[TD="align: right"]2019[/TD]
[TD="align: right"]2020[/TD]
[TD="align: right"]2021[/TD]
[TD][/TD]
[TD="align: right"]Jan-18[/TD]
[TD="align: right"]Feb-18[/TD]
[TD="align: right"]Mar-18[/TD]
[TD="align: right"]Apr-18[/TD]
[TD="align: right"]May-18[/TD]
[TD="align: right"]Jun-18[/TD]
[/TR]
[TR]
[TD]Start[/TD]
[TD]End[/TD]
[TD]Value[/TD]
[TD]Value[/TD]
[TD]Value[/TD]
[TD]Value[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]5/03/2018[/TD]
[TD="align: right"]10/06/2020[/TD]
[TD] $ 100,000.00[/TD]
[TD] $ 180,000.00[/TD]
[TD] $ 295,000.00[/TD]
[TD] $ -
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 755"]
<colgroup><col><col><col span="3"><col><col><col span="4"></colgroup><tbody>[TR]
[TD] [/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]
Thank you very much

Hope someone can help

 

[Sahitya]

I have worked on the formula and formulated in excel. please let me know how to place the excel