I'm looking for some much needed help after hours of looking.

I have lets call it a task list of things to do. Columns A B and C have text, column D is a number (hours it will take me to complete task) and column E is empty and use it to 'tick' off my list by typing 'x' with conditional formatting this in turn 'greys out' the whole row - and it also removes the number on this row from my chart, which is exactly what I want, but I also want my total values of column D to reflect this. I put my SUM in column F.

I want to SUM (or add together all values in column D) which is fine with a simple SUM formula, but I want sheets to minus any number that I have completed with my 'x' in column E. I'm sure it must be possible as it does this automatically with any chart I add, but just want 1 cell with the answer?

FYI the conditional formatting I use is =$E10="x" making the row grey if there's an 'x' there and I also use =ISBLANK(#REF!) if no 'x' is present.

  • OK so I have figured out how to do the exact opposite of what I'm after using the formula =SUMIF(E11:E20,"x",D11:D20) this adds all the values in column D together but only if Column E has an 'x' input, I want it to Add all values in column D that dont have an x in column E Feb 20, 2021 at 11:41

1 Answer 1


OK Thanks for stopping by, I finally did it with =SUMIF(E11:E20,"<>x",D11:D20) where <> is not equal too.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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