I have the following formula to count cells within a range B10:B1095 which do not contain certain strings

  B10:B1095, "<>",
  B10:B1095, "<>Manual",
  B10:B1095, "<>TODO")

How can I add an additional condition to check if the cells in a range do not contain a specific sub-string?

1 Answer 1


not sure what exactly you are after but you can just add as many conditions as you need like:

=COUNTIFS(B10:B1095, "<>",
          B10:B1095, "<>Manual",
          B10:B1095, "<>TODO",
          B10:B1095, "<>x",
          B10:B1095, "<>y",
          B10:B1095, "<>z")

or maybe you need this:

=IF(COUNTIFS(B10:B1095, "<>",
             B10:B1095, "<>Manual",
             B10:B1095, "<>TODO"), COUNTIFS(B10:B1095, "<>x",
                                            B10:B1095, "<>y",
                                            B10:B1095, "<>z"))

or this one:

=COUNTIFS(B10:B109; "<>";
          B10:B109; "<>Manual";
          B10:B109; "<>*TODO*")
  • "<>TODO" means cell should not contain "TODO" but what if cell contains "TODOD", then cell will be counted, so I need sub-string argument ... <> means not this string, I need, not this sub-string
    – the_prole
    Aug 17, 2018 at 21:53
  • @the_prole can you share a copy of your sheet? it would be so much faster... I know what everything in that formula means... what I do not know is what exactly do you want to accomplish. could you at least add some examples? see my updated answer
    – user0
    Aug 17, 2018 at 23:18
  • What does * TODO * do. Does it mean substring?
    – the_prole
    Aug 18, 2018 at 4:40
  • 1
    @the_prole it means it will search for string in the cell so if the cell consists of "TODO" or "TODODODO" it will count for it
    – user0
    Aug 18, 2018 at 20:01
  • That's exactly I meant by substring, a string inside a string, thanks
    – the_prole
    Aug 19, 2018 at 5:52

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.