Auto Calculate Amount Remaining

[Capsaicin Burn]

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[TD]F
[/TD]
[TD]G
[/TD]
[TD]H
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Debt
[/TD]
[TD]Payment
[/TD]
[TD]Due
[/TD]
[TD]Jun
[/TD]
[TD]Jul
[/TD]
[TD]Aug
[/TD]
[TD]Sep
[/TD]
[TD]Oct
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]Loan
[/TD]
[TD]$75
[/TD]
[TD]1st
[/TD]
[TD="align: center"]Y
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]CC1
[/TD]
[TD]$25
[/TD]
[TD]4th
[/TD]
[TD="align: center"]Y
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]CC2
[/TD]
[TD]$25
[/TD]
[TD]10th
[/TD]
[TD="align: center"]Y
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]CC3
[/TD]
[TD]$30
[/TD]
[TD]15th
[/TD]
[TD="align: center"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]Internet
[/TD]
[TD]$75
[/TD]
[TD]20th
[/TD]
[TD="align: center"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]Cell
[/TD]
[TD]$100
[/TD]
[TD]21st
[/TD]
[TD="align: center"]Y
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD]Ins
[/TD]
[TD]$100
[/TD]
[TD]24th
[/TD]
[TD="align: center"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9
[/TD]
[TD]Car
[/TD]
[TD]$350
[/TD]
[TD]28th
[/TD]
[TD="align: center"]Y
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10
[/TD]
[TD]Total
[/TD]
[TD]$625
[/TD]
[TD]Remaining
[/TD]
[TD="align: center"]$205
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


















Columns A,B and C never change

My dilemma is making row 10 auto calculate and populate what is still due each month.

I simply mark each debt with a Y to indicate "Yes it has been paid". A blank cell indicates it has NOT been paid.

Currently I just add it up manually after each payment is made. I would like it to figure out, and update, the amount remaining as I make payments. How to make it calculate automatically has baffled me to date.

Any Help would be greatly appreciated!

Jim

 

[Capsaicin Burn]

Every month I pay out $780

 

[Fazza]

Yes the "" is as you say.

Similar to the comments from kweaver, exactly what you want could be clearer.
And depending on how much data you have, maybe there is a better way to set up the data - swap the rows & columns.

regards

 

[Twollaston]

Thank you Twollaston. My result was a zero

you have to add your ranges for the data you are using where it says B:B and D:D and the formula will work

specifically this
=SUMIF(D$2:D9,"",$B$2:$B9)
you're going to have to update it everytime you have more lines of data
but if you move your remaining amount lines to the top, then you can make it so it will get the right amount no matter how many rows you add, and you won't have to keep redoing the formula ranges

 

[kweaver]

The difference is that Fazza is treating every month's payment against the $780 total. I'm treating the months as paying off the $780.

Here's mine at the top and Fazza's at the bottom:

[TABLE="width: 648"]
<colgroup><col span="8"></colgroup><tbody>[TR]
[TD]Debt[/TD]
[TD]Payment[/TD]
[TD]Due[/TD]
[TD]Jun[/TD]
[TD]Jul[/TD]
[TD]Aug[/TD]
[TD]Sep[/TD]
[TD]Oct[/TD]
[/TR]
[TR]
[TD]Loan[/TD]
[TD="align: right"]$75[/TD]
[TD]1st[/TD]
[TD]Y[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]CC1[/TD]
[TD="align: right"]$25[/TD]
[TD]4th[/TD]
[TD]Y[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]CC2[/TD]
[TD="align: right"]$25[/TD]
[TD]10th[/TD]
[TD]Y[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]CC3[/TD]
[TD="align: right"]$30[/TD]
[TD]15th[/TD]
[TD] [/TD]
[TD]Y[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Internet[/TD]
[TD="align: right"]$75[/TD]
[TD]20th[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Y[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Cell[/TD]
[TD="align: right"]$100[/TD]
[TD]21st[/TD]
[TD]Y[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Ins[/TD]
[TD="align: right"]$100[/TD]
[TD]24th[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD]Car[/TD]
[TD="align: right"]$350[/TD]
[TD]28th[/TD]
[TD]Y[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Total[/TD]
[TD="align: right"]$780[/TD]
[TD]Remaining[/TD]
[TD]$205[/TD]
[TD]$175[/TD]
[TD]$100[/TD]
[TD]$100[/TD]
[TD]$0[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Debt[/TD]
[TD]Payment[/TD]
[TD]Due[/TD]
[TD]Jun[/TD]
[TD]Jul[/TD]
[TD]Aug[/TD]
[TD]Sep[/TD]
[TD]Oct[/TD]
[/TR]
[TR]
[TD]Loan[/TD]
[TD="align: right"]$75[/TD]
[TD]1st[/TD]
[TD]Y[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]CC1[/TD]
[TD="align: right"]$25[/TD]
[TD]4th[/TD]
[TD]Y[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]CC2[/TD]
[TD="align: right"]$25[/TD]
[TD]10th[/TD]
[TD]Y[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]CC3[/TD]
[TD="align: right"]$30[/TD]
[TD]15th[/TD]
[TD] [/TD]
[TD]Y[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Internet[/TD]
[TD="align: right"]$75[/TD]
[TD]20th[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Y[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Cell[/TD]
[TD="align: right"]$100[/TD]
[TD]21st[/TD]
[TD]Y[/TD]
[TD]Y[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Ins[/TD]
[TD="align: right"]$100[/TD]
[TD]24th[/TD]
[TD] [/TD]
[TD]Y[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD]Car[/TD]
[TD="align: right"]$350[/TD]
[TD]28th[/TD]
[TD]Y[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD]Total[/TD]
[TD="align: right"]$780[/TD]
[TD]Remaining[/TD]
[TD]$205[/TD]
[TD]$550[/TD]
[TD]$705[/TD]
[TD]$780[/TD]
[TD]$330[/TD]
[/TR]
</tbody>[/TABLE]

 

[Fazza]

thanks kweaver. that is helpful

And maybe the ideal result is different again..

 

[Capsaicin Burn]

Thanks again kweaver. As FAZZA stated, I certainly could have been more clear with my question. I now understand what and how you were calculating it. I really do appreciate yours and everyone else's time and help! This is an amazing forum!

 

[Twollaston]

The difference is that Fazza is treating every month's payment against the $780 total. I'm treating the months as paying off the $780.

Here's mine at the top and Fazza's at the bottom:

[TABLE="width: 648"]
<tbody>[TR]
[TD]Debt[/TD]
[TD]Payment[/TD]
[TD]Due[/TD]
[TD]Jun[/TD]
[TD]Jul[/TD]
[TD]Aug[/TD]
[TD]Sep[/TD]
[TD]Oct[/TD]
[/TR]
[TR]
[TD]Loan[/TD]
[TD="align: right"]$75[/TD]
[TD]1st[/TD]
[TD]Y[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]CC1[/TD]
[TD="align: right"]$25[/TD]
[TD]4th[/TD]
[TD]Y[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]CC2[/TD]
[TD="align: right"]$25[/TD]
[TD]10th[/TD]
[TD]Y[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]CC3[/TD]
[TD="align: right"]$30[/TD]
[TD]15th[/TD]
[TD][/TD]
[TD]Y[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Internet[/TD]
[TD="align: right"]$75[/TD]
[TD]20th[/TD]
[TD][/TD]
[TD][/TD]
[TD]Y[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Cell[/TD]
[TD="align: right"]$100[/TD]
[TD]21st[/TD]
[TD]Y[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Ins[/TD]
[TD="align: right"]$100[/TD]
[TD]24th[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD]Car[/TD]
[TD="align: right"]$350[/TD]
[TD]28th[/TD]
[TD]Y[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Total[/TD]
[TD="align: right"]$780[/TD]
[TD]Remaining[/TD]
[TD]$205[/TD]
[TD]$175[/TD]
[TD]$100[/TD]
[TD]$100[/TD]
[TD]$0[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Debt[/TD]
[TD]Payment[/TD]
[TD]Due[/TD]
[TD]Jun[/TD]
[TD]Jul[/TD]
[TD]Aug[/TD]
[TD]Sep[/TD]
[TD]Oct[/TD]
[/TR]
[TR]
[TD]Loan[/TD]
[TD="align: right"]$75[/TD]
[TD]1st[/TD]
[TD]Y[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]CC1[/TD]
[TD="align: right"]$25[/TD]
[TD]4th[/TD]
[TD]Y[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]CC2[/TD]
[TD="align: right"]$25[/TD]
[TD]10th[/TD]
[TD]Y[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]CC3[/TD]
[TD="align: right"]$30[/TD]
[TD]15th[/TD]
[TD][/TD]
[TD]Y[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Internet[/TD]
[TD="align: right"]$75[/TD]
[TD]20th[/TD]
[TD][/TD]
[TD][/TD]
[TD]Y[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Cell[/TD]
[TD="align: right"]$100[/TD]
[TD]21st[/TD]
[TD]Y[/TD]
[TD]Y[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Ins[/TD]
[TD="align: right"]$100[/TD]
[TD]24th[/TD]
[TD][/TD]
[TD]Y[/TD]
[TD][/TD]
[TD][/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD]Car[/TD]
[TD="align: right"]$350[/TD]
[TD]28th[/TD]
[TD]Y[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD]Total[/TD]
[TD="align: right"]$780[/TD]
[TD]Remaining[/TD]
[TD]$205[/TD]
[TD]$550[/TD]
[TD]$705[/TD]
[TD]$780[/TD]
[TD]$330[/TD]
[/TR]
</tbody>[/TABLE]

Yeah it's hard to tell what happens to the unpaid stuff, should it roll over, or will it always be paid before the next month starts?

I'm guessing these are monthly expenses that pop up in full every month, and likely all should be paid before the next month is calculated

 

[Capsaicin Burn]

I think the reason it threw me off was that I never have a remaining balance at the end of the month and I didn't think of that perspective. However, this is indeed good information if I ever find myself in that situation. Thanks again!

 

[Capsaicin Burn]

The debts don't change often but they do change. Putting the row at the top to avoid the editing it is genius. I would have never thought of that or realized the value of that simple mod. That is why I ask the experts! Thanks once again!

 

[Capsaicin Burn]

you have to add your ranges for the data you are using where it says B:B and D:D and the formula will work

specifically this
=SUMIF(D$2:D9,"",$B$2:$B9)
you're going to have to update it everytime you have more lines of data
but if you move your remaining amount lines to the top, then you can make it so it will get the right amount no matter how many rows you add, and you won't have to keep redoing the formula ranges

The debts don't change often but they do change. Putting the row at the top to avoid editing it is genius. I would have never thought of that or realized the value of that simple mod. That is why I ask the experts! Thanks once again!