Subtract if, add if

[Williamsj214]

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 says "direct deposit, atm deposit" add cell b3 and b4.


It needs to be all in a single formula.

[#2]

@Williamsj214 

Is "gas, gift, entertainment" a single value in cell B2, or do you mean that B2 contains one of those words?

[#3]

One of those words

[#4]

@Williamsj214 

=IF(OR(B2={"gas", "gift", "entertainment"}),A4-B3,IF(OR({"direct deposit", "atm deposit"}),B4+B3,""))

[#5]

@Williamsj214 

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.