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