IF a cell is Y, subtracting it's neighbor cell from a separate value.

[lilli_ann]

I am working on a personal budget, and would like to have a formula that tracks whether or not a bill has been paid and reports back the total amount left to be paid:
If column B cell is "Y", then subtract column A value from a column D value, and then have this repeat for a certain number of cells until the second D value is 0.


For specifics:
IF B42-B52="Y" (This tells me I have paid the bill)
Then Subtract D42-A42:52 with the result landing in D45

Value in D42 is the total amount of SUM(A42:52)
Value in D45 should end up being D42-A42:52 but ONLY subtracting a value when it's corresponding B value="Y"

I am aware that this is an overcomplicated way of having it laid out. However, I really like to have the "total amount spent/vs total amount to pay" readily visible. If anyone can help me with the formula above or may have an idea for simplifying the layout of it, then I would be eternally grateful as I have been trying to figure it out for days!

--Lilli Ann

[Ace_XL]

Maybe..

In D45

Please Login or Register  to view this content.

[rossg]

formula above is all good but i dont think the formula in D42 is doing what you want it to...

shouldnt all the $ amount be adding up on top of each other?

[lilli_ann]

Thanks Rossg, you're right, it wasn't doing it quite right. And Ace_XL, that formula probably would work, and it was close to what I had on my own. But my version of excel won't accept the SUMIF function. I was just trying to use "IF" commands, so that's probably why I couldn't get it to work.

Ack! I don't think I'll be able to get it to do what I want. Haha thank you guys for your help! :D

[lilli_ann]

Thanks Rossg, you're right, it wasn't doing it quite right. And Ace_XL, that formula probably would work, and it was close to what I had on my own. But my version of excel won't accept the SUMIF function. I was just trying to use "IF" commands, so that's probably why I couldn't get it to work.

Ack! I don't think I'll be able to get it to do what I want. Haha thank you guys for your help! :D