Formula for checking total current balance of several lists

[fumusic]

Hi, could anyone please help? I have no idea how to work out this formula.
I have bought 3 items and have set up a scheduled payment for each item.
Each item is paid on a different day of the month.
I have a spreadsheet which lists the scheduled payment dates, the payments, and the remaining balance for each item.
In cell B28 I would like a formula which works out the current remaining balance.
So if today was 16th March 2025, that box should show a total of 1550. 370 (from C4) + 380 (from F2) + 800 (from I3).
Thank you for any help.

[TMS]

You seem to have a mix of US and UK dates, which is confusing. I have copied them to Sheet2 and made them consistent.

The solution might be ( is ) simpler if you include all the payments for Item A. Then you can sum all outstanding payments.

See the formula on Sheet2

[fumusic]

hi tms, i can't seem to find your formula in b28. where is it? if you put the formula in these messages i can put it in myself. i don't quite follow your explanation. thank you.

[TMS]

See Sheet2, cell B44

=LET(v,VSTACK(A2:B41,D2:E41,G2:H41),fv,FILTER(v,(INDEX(v,,1)<>"")*(INDEX(v,,1)>TODAY())+0),SUM(INDEX(fv,,2))) 

Or just

=LET(v,VSTACK(A2:B41,D2:E41,G2:H41),fv,FILTER(v,(INDEX(v,,1)<>"")*(INDEX(v,,1)>TODAY())),SUM(INDEX(fv,,2))) 

[fumusic]

thank you tms. works perfectly. sorry i didnt spot sheet 2!
i've noticed that your formula does not refer to the remaining balance columns at all. so how does it know how much is owed? i can see that the formula works but would to know how it does.

[TMS]

You're welcome. Thanks for the rep.

The formula is not looking at the calculated remaining balance columns. it simply sums the values that are after the current date. Hence the need to complete the table for Item A.

I'm sure there is a way to pick out the value from the calculated remaining balance columns but, at the time, it didn't come to me.

[fumusic]

i understand now. so the scheduled payments columns need to be filled in fully so that the total owed can be worked out. thanks again.

[TMS]

This seems to work using the calculated remaining values. That said, you will still need to complete the table.

=LET(v,VSTACK(A2:C41,D2:F41,G2:I41),fv,FILTER(v,(INDEX(v,,1)<>"")*(INDEX(v,,1)>TODAY()-28)),SUM(FILTER(INDEX(fv,,3),(INDEX(fv,,1)>TODAY())*INDEX(fv,,1)<=TODAY())))