Excel Formula to Return Value based on date and contract legnth

[DJ'sGiGi]

I'm struggling with what I thought would be a simple formula. In column E of a spreadsheet, I have a the years contracts begin. In column G, I have the number of years the contract will last and column I has the annual payment amount. On a second spreadsheet, I have 5 years in columns G thru K (2018-2022). I'm trying to create a formula that will return the annual payment amount for each contract in the year the payment will be received.

For instance, I have a 2 year contract that begins in 2019 and pays $100 annually. The formula should return $100 in columns H and I on the 2nd spreadsheet. I have tried sumif, sumproduct, and nested if statements and none seem to be working. Any assistance is greatly appreciated.

I hesitate to put the formulas that I have because they aren't working, but I'll include them anyway.

=SUMPRODUCT(Assumptions!$G$12:$G$71>=1,Assumptions!$E$12:$E$71>='Model Output'!G$4)*Assumptions!$I$12:$I$71

=SUM(IF(AND(Assumptions!$G$12:$G$71>=1,Assumptions!$E$12:$E$71<('Model Output'!G$4+Assumptions!$G13)),Assumptions!$I13,0))

=SUMIFS(Assumptions!$I$12:$I$71,Assumptions!$G$12:$G$71,">=1",Assumptions!$E$12:$E$71,"="&G$4,Assumptions!$E$12:$E$71,"<"&(G$4+Assumptions!$G14))

 

[Special-K99]

The range of years to which the payment should be applied will begin at the start year and finish at the start year+(length-1)

No SUMIFs SUMPRODUCTs needed, just a simple IF(AND(...

in Sheet2!G2
=IF(AND(G$1>=Sheet1!$E2,G$1<=Sheet1!$E2+(Sheet1!$G2-1)),Sheet1!$I2,"")
copy across the column

and presumably down for other contracts

 

[DJ'sGiGi]

Special-K99, this works perfectly!! I was really over thinking this one.

The range of years to which the payment should be applied will begin at the start year and finish at the start year+(length-1)

No SUMIFs SUMPRODUCTs needed, just a simple IF(AND(...

in Sheet2!G2
=IF(AND(G$1>=Sheet1!$E2,G$1<=Sheet1!$E2+(Sheet1!$G2-1)),Sheet1!$I2,"")
copy across the column

and presumably down for other contracts