Forum Discussion
Subtract items from running total
I’m trying to create a spreadsheet that will easily show the dollar amount remaining as items are purchased from a set dollar amount.
I have 35 rows of items with a price associated to each item in a column and auto sum the total of that column.
Another column is if we purchased the item indicated by either “No” or “Yes” with the default for each item set to “No”.
I would like it so as we purchase items the amount of that item subtracted from the items total amount.
So if cell B36 is the total of cells B1-B35 and cell C1=”Yes” and C15=”Yes” subtract that item price from B36.
Thanks for any help!
Joe,
You can use a formula like this:
=SUMIF(C2:C13,"No",B2:B13)
To add only the item prices that have a purchase status of no.
Please find the attached file.
6 Replies
Joe,
You can use a formula like this:
=SUMIF(C2:C13,"No",B2:B13)
To add only the item prices that have a purchase status of no.
Please find the attached file.
- This should be incorporated from page to page as you use it for your set of home books or a small business. Works but needs to be able to move some of that data out 3 months as being paid in the future. IMO
Not sure i need the "No" formula as I did get this to work but it's only for one row,
=IF(OR(C3={"Yes"}),B14-B3)
Seems like if i could add the addtional rows it would work. I need to add these to the formula
IF(OR(C4={"Yes"}),B14-B4)
IF(OR(C5={"Yes"}),B14-B5)
IF(OR(C6={"Yes"}),B14-B6)
so forth for the remaining rows.
I've included the excel file.
I tried this with no luck.
=IF(OR(C3={"Yes"}),B14-B3)(OR(C4={"Yes"}),B14-B4)
Joe,
The logic of my formula in the previous answer is simple and straightforward, and it's the best solution for your question!
What you want is to subtract any purchased item from the total, so the best and shortest way or algorithm is to sum only the items that ware not purchased so that any purchased item won't be included in the remaining purchases total.
Put this formula in cell B15 and see how it works:
=SUMIF(C3:C12,"no",B3:B12)
