Forum Discussion
Diffrence in values
Hello,
I am creating my own Bill Tracker with a cell that has the $ amount that i owe and a cell that when i pay that amount i check it to show paid.At the bottom of the $ cells i have a total amount of bills that i owe and in the cell below that i would like to know the differance between what i owe and what is left after i have checked off "Paid".I have already set the rule once i check it another cell shows paid but when i do that i want to know how to get it to deduct it at the bottom with the differance.Hope i made scense and Thank You.
5 Replies
Hi Gerry,
Don't know which formulas you use now, SUMIF or SUMIFS or SUMPRODUCT could give you any desirable combination. For example, let assume you have in A1:A10 dates of the bills, in column B amounts and in column C the status ("Paid" or empty).
Not paid sum could be calculated as
=SUMIF(C1:C10,"<>Paid",B1:B10)
Not paid amount of May's bills
=SUMPRODUCT((B1:B10)*(C1:C10<>"Paid")*(MONTH(A1:A10)=5))
And so on, above is just an idea
Hello Sergei,
I am very new to Excel and not sure which formula to use,i will give a better example of what i am trying to accomplish.In D3 through D30 i have the amounts of my bills,
I have set up this formula =IF(E3:E30="x","PAID","") in A3 through A30.In D31 i have the totals of column D,So my goal is once i check off a bill i would like to see the difference between what i owe and what is paid in column D32.
Thank You for your time.
Gerry, when in D32 the sum of non-paid bill will be
=SUMIF(E3:E30,"<>x",D3:D30)
If you mean the what you owe is the sum of all not paid bills and you'd like to have the differrence between above and paid bills when
=SUMIF(E3:E30,"<>x",D3:D30)-SUMIF(E3:E30,"x",D3:D30)
