Return a value for a date that is between two dates

I have a debt schedule that only has data rows for payment intervals that are semi-annually (i.e. 6/30 and 12/31 and nothing for the months in between). I need a Balance Sheet to look up the monthly principal balance.

For example, if payments are on 6/30 and 12/31 then the principal balance should be the same for Dec - May and the same for Jun - Nov. I am creating a template and need the Balance Sheet to look up these balances and to do so if the interval changes or debt issue date changes under different scenarios.

I've tried SUMPRODUCT, IF(AND), IF with multiple IF criteria. I haven't been able to find the right combination or structure.

Thanks in advance.

Jay

EDIT: added example file, also, forgot that one sheet has time vertically and the other horizontally.

Hi and welcome to the forum :0

Hard to offer suggestions without seeing what you are working with?`

However, if the value will only ever be in once within each time frame, try using a sumifS() function...

=sumifS(sum range, range with dates,">="&start-date, range with dates,"<="&end-date,

Can you upload an example workbook (use false data) to help us see excatly what you want to achieve. Can you also include a manually entered answer or two so we can make sure we get the correct result.

I used this in E47, copied across...

=E46-SUMIFS('Debt - Assump'!$H:$H,'Debt - Assump'!$B:$B,">="&'Balance Sheet'!E$6,'Debt - Assump'!$B:$B,"<"&'Balance Sheet'!F$6)