Forum Discussion

Williamsj214's avatar
Williamsj214
Copper Contributor
Apr 30, 2021

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


It needs to be all in a single formula.
excel

4 Replies

      • mathetes's avatar
        mathetes
        Gold Contributor
        Apr 30, 2021

        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.

Resources