Google-sheets – How to not count cell if cell contains sub-string

google sheets

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

=COUNTIFS(
  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?

Best Answer

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*")
Related Topic