Forum Discussion
Subtract if, add if
I'm attempting to get excel to subtract/ add cell from another cell if a 3rd cell meets a criteria list. ie if cell b2 says "gas, gift, entertainment" then subtract cell b3 from a4, or if cell b2 s...
Is "gas, gift, entertainment" a single value in cell B2, or do you mean that B2 contains one of those words?
- One of those words
If I'm not mistaken, given that you were just giving examples, the actual criteria lists for debits/credits will be longer and more varied.
And that could mean that this approach is potentially setting you up for frustration and difficulty.
- What happens, for example, if somebody enters "entertanment" or "entertainment " (a misspelling or a hidden space)?
- What happens if it's "fuel" instead of "gas"?
- what about some category or description that is brand new?
My suggestion would be to have:
- a single column that is headed "Description", which could contain any of those words as well as any others that might come along
- a second column with a drop-down (data validation) selection offering "Credit" or "Debit"
- third column with the amount
- the fourth column, the running balance, in which your IF conditional formula is based on the contents of that second column, adds if it's a credit; subtracts if debit.
=IF(B3="Debit",D2-C3,D2+C3)
Or something to that effect. That would be a far more robust and reliable method. Less "clever" perhaps, but "clever" has a way of coming back to bite.
=IF(OR(B2={"gas", "gift", "entertainment"}),A4-B3,IF(OR({"direct deposit", "atm deposit"}),B4+B3,""))
