I have a table of payments to calculate interest. The column where the payment is applied takes its values from the column where the invoice totals are listed, however, the payment is applied k days after the period ends.
I had partial success doing it using this formula:
IFERROR(INDEX($F$12:$F$25,MATCH(D12,$G$12:$G$25,1)),0)
Where column G is a helper column with the dates of payment, basically period end + k, but since it only accounts for the period end, in monthly and semimonthly periods, sometimes the nearest lower date of payment was on the same period, so I MUST also account for the period start for this to not happen. I've been helped using an array formula like this:
=IFERROR(INDEX(F:F,SMALL(IF(F$12:F12>0,ROW(F$12:F12)),COUNT(1/((C$12:C12-C$12>C$7)+(D$12:D12-C$12>C$7))))),0)
And it works well and it does not require a helper column. But since it's an array formula, and this table is not for my usage, that's not suitable.
I would like to know if I can do this without an array formula and built-in excel 2013 functions.
Edit: This formula does it:
=SUMPRODUCT(($D$12:$D$25+$C$7>=C12)*($D$12:$D$25+$C$7<=D12)*($F$12:$F$25))
But if there are blanks in column D that result from a formula, it returns an error. So the following formula is more stable:
=SUMIFS($F$12:$F$25,$D$12:$D$25,">="&C12-$C$7,$D$12:$D$25,"<="&D12-$C$7)
This one effectively places the payment rows within the range of dates it belongs to.
