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

 

[kweaver]

when I totaled the B column, I got $780. ??

D10:

=SUMPRODUCT(("Y"<>$D$2:D$9)*$B$2:$B$9)

E10:

=D10-SUMPRODUCT((E2:E9="Y")*$B$2:$B$9)

And fill E column across.

 

[Capsaicin Burn]

Thanks kweaver. Your calculation is correct. Those numbers are fictitious and I clearly missed something in my calculation in this example.

 

[Twollaston]

[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

=SUMIF(D:D,"",B:B)

Make sure your total in column B is accurate

 

[Fazza]

what about =SUMIF(D$2:D$9,"",$B$2:$B$9)

 

[kweaver]

[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]
</tbody>[/TABLE]


Is what I got.

 

[Capsaicin Burn]

Thank you Twollaston. My result was a zero

 

[Capsaicin Burn]

Thank you Fazza. It worked perfectly. I need to do a little research to understand why it worked though. Does the "" part indicated an empty cell?

 

[kweaver]

I guess the question I have is are you always paying out of the 780 every month? That's not what I calculated. I calculated that you were catching up with the payments month by month and show what's still remaining.

 

[Capsaicin Burn]

I suspect it works. I'm slightly confused about the reason for the seemingly great differences of D10 and E10 though. I have a lot to learn though.