2

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.

4

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".

enter image description here

  • 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
3

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.