Excel formula/function to subtract until 0

[agrych]

	A	B	C	D	E	AA	AB	AC
1						Invoices
2	Year	Remaining	Task	Funding	Invoice	Task	Fund	Amount
3	2017	3014.03	97	M	0
5	2015	6460.71	98	L6	60.71	98	L6	60.71
7	2018	580.28	99	F	0	100	A	5600.74
10	2019	6956.39	99	F	0
11	2017	5510.74	100	A	5510.74
13	2018	8737.52	100	A	90
14	2019	5000.00	100	A	0
15

<tbody>
</tbody>

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.

<tbody>
</tbody>

 

[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)