Excel formula/function to subtract until 0

[agrych]

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]AA[/TD]
[TD="align: center"]AB[/TD]
[TD="align: center"]AC[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Invoices[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Year[/TD]
[TD]Remaining[/TD]
[TD]Task[/TD]
[TD]Funding[/TD]
[TD]Invoice[/TD]
[TD]Task[/TD]
[TD]Fund[/TD]
[TD]Amount[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]2017[/TD]
[TD]3014.03[/TD]
[TD]97[/TD]
[TD]M[/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]2015[/TD]
[TD]6460.71[/TD]
[TD]98[/TD]
[TD]L6[/TD]
[TD]60.71[/TD]
[TD]98[/TD]
[TD]L6[/TD]
[TD]60.71[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]2018[/TD]
[TD]580.28[/TD]
[TD]99[/TD]
[TD]F[/TD]
[TD]0[/TD]
[TD]100[/TD]
[TD]A[/TD]
[TD]5600.74[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]2019[/TD]
[TD]6956.39[/TD]
[TD]99[/TD]
[TD]F[/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]2017[/TD]
[TD]5510.74[/TD]
[TD]100[/TD]
[TD]A[/TD]
[TD]5510.74[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]2018[/TD]
[TD]8737.52[/TD]
[TD]100[/TD]
[TD]A[/TD]
[TD]90[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]2019[/TD]
[TD]5000.00[/TD]
[TD]100[/TD]
[TD]A[/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 550"]
<tbody>[TR]
[TD]From visible rows, I need to subtract the Invoice amount $5600.74 from the remaining funds for Task 100 Fund A until invoice is zero - recording the amount of the invoice used under the invoice column. Notice some rows are hidden as this is a filtered table.

Having a hard time writing a formula for cells in column E to handle subtracting the invoice amount until it has all been applied to the remaining funds (oldest year first). Perhaps a VBA function could handle this?

Any suggestions would be greatly appreciated.[/TD]
[/TR]
</tbody>[/TABLE]

 

[CyrusTheVirus]

Welcome to the forums.

Why are there hidden rows? Can you show them for example's sake?

 

[agrych]

The hidden rows have a remaining value of 0 in column B
For example:
A B C D E
Row 4: 2014 0 98 L6 0
Row 6: 2018 0 99 F 0
Row 12: 2017 0 100 A 0

 

[CyrusTheVirus]

Enter with Ctrl+Shift+Enter in Cell E3:

=IF($B3=0,0,IF(INDEX($AE$3:$AE$4,MATCH($C3&$D3,$AC$3:$AC$4&$AD$3:$AD$4,0))-SUMIFS($E$2:$E2,$D$2:$D2,$D3)=0,0,MIN($B3,INDEX($AE$3:$AE$4,MATCH($C3&$D3,$AC$3:$AC$4&$AD$3:$AD$4,0))-SUMIFS($E$2:$E2,$D$2:$D2,$D3))))

Will this work for you? The errors that appear are due to the Task/Funding classes not within the Task/Fund range in columns AC/AD, so you'd either need to 1) add them, and then alter the formula, 2) ignore the errors, or 3) wrap the formula within a IFERROR function.

Reach out with any questions.

 

[agrych]

Thank you! I had to modify it a bit to get it to work for me.
The following works as desired.

=IFERROR(IF($B3=0,0,IF(INDEX($AC$3:$AC$14,MATCH($C3&$D3,$AA$3:$AA$14&$AB$3:$AB$14,0))-SUMIFS($E$2:$E2,$C$2:$C2,$C3,$D$2:$D2,$D3)=0,0,MIN($B3,INDEX($AC$3:$AC$14,MATCH($C3&$D3,$AA$3:$AA$14&$AB$3:$AB$14,0))-SUMIFS($E$2:$E2,$D$2:$D2,$D3)))),0)

 

[agrych]

Sorry, this is the correct formula that worked for me:
=IFERROR(IF($B5=0,0,IF(INDEX($AC$3:$AC$14,MATCH($C5&$D5,$AA$3:$AA$14&$AB$3:$AB$14,0))-SUMIFS($E$2:$E4,$C$2:$C4,$C5,$D$2:$D4,$D5)=0,0,MIN($B5,INDEX($AC$3:$AC$14,MATCH($C5&$D5,$AA$3:$AA$14&$AB$3:$AB$14,0))-SUMIFS($E$2:$E4,$C$2:$C4,$C5,$D$2:$D4,$D5)))),0)