Auto Calculate Amount Remaining

[Capsaicin Burn]

	A	B	C	D	E	F	G	H
1	Debt	Payment	Due	Jun	Jul	Aug	Sep	Oct
2	Loan	$75	1st	Y
3	CC1	$25	4th	Y
4	CC2	$25	10th	Y
5	CC3	$30	15th
6	Internet	$75	20th
7	Cell	$100	21st	Y
8	Ins	$100	24th
9	Car	$350	28th	Y
10	Total	$625	Remaining	$205

<tbody>
</tbody>

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]

	A	B	C	D	E	F	G	H
1	Debt	Payment	Due	Jun	Jul	Aug	Sep	Oct
2	Loan	$75	1st	Y
3	CC1	$25	4th	Y
4	CC2	$25	10th	Y
5	CC3	$30	15th
6	Internet	$75	20th
7	Cell	$100	21st	Y
8	Ins	$100	24th
9	Car	$350	28th	Y
10	Total	$625	Remaining	$205

<tbody>
</tbody>

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]

Debt	Payment	Due	Jun	Jul	Aug	Sep	Oct
Loan	$75	1st	Y
CC1	$25	4th	Y
CC2	$25	10th	Y
CC3	$30	15th		Y
Internet	$75	20th			Y
Cell	$100	21st	Y
Ins	$100	24th					Y
Car	$350	28th	Y
Total	$780	Remaining	$205	$175	$100	$100	$0

<colgroup><col span="8"></colgroup><tbody>
</tbody>

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.