# Excel formula to subtract from total when an X appears in a cell

I have a small spreadsheet to track the total cost of something. I have cells `C6:C19` with dollar amounts in them. I have cell `C21` showing that total. I've created a conditional rule for the cells `B6:B19`. But if I put an X in those cells, it strikes out the value in column `C`.

Now I'd like when I put an x in that it also subtracts that amount from the total in cell `C21`, but I can't figure out how to do so.

You can use the `SUMIF` function to add up only the values in column C that do not have an X in the same row in in column B. Change the formula in cell C21 to

``````=SUMIF(B6:B19,"<>X",C6:C19)
``````

This calculates the sum of all values in the range C6:C19 where the equivalent value in the range B6:B19 is not "X".

• Awesome! Looks like either would work but I went with the sumif. I was trying to use that before but didn't have syntax right. Thanks! – gec5741 Dec 11 '19 at 18:52
• Don't forget to mark the answer as correct if it solves your problem – cybernetic.nomad Dec 11 '19 at 19:40

You can use `SUMPRODUCT` to achieve this:

``````=SUMPRODUCT(--(B6:B15<>"X"),C6:C19)
``````

This will basically add all values in `C6:C19`, unless the corresponding cell in `B6:B19` has an `"x"` in it

More info on `SUMPRODUCT` can be found here

The above formula takes advantage of the fact that expressions returning `TRUE/FALSE` (in this case `B6:B15<>"X"`) essentially return `1/0`