There are several different ways to count cells in Google Sheets conditionally. However, most techniques look for a positive match to a condition. Let’s explore the opposite, where we count cells that do not match a condition.
Follow along with these examples using this live spreadsheet.
We will be using COUNTIF in these examples, which is the most efficient method of conditional counting as it combines an IF statement with the COUNT function.
Contents
Count the Number of Cells Without Certain Text (Exact Match)
First, let’s start with the simplest example.
In the image above, we count the number of days that are not sunny. This formula looks for a complete match, meaning it will count cells unless they contain the entire word and only that word with no spaces or other characters. These techniques use the < and > operators together to mean does not contain. Capitalization does not matter with COUNTIF, so “sunny” would return the same result as “SUNNY” or “Sunny.”
Count the Number of Cells That Don’t End With Certain Text
Previously, we were matching all of the cell’s contents. Now let’s look at comparing the end of a string of text.
To match any characters before the word sun, we use wildcards. Wildcards represent different characters in Google Sheets. An asterisk (*) is any number of characters, a question mark (?) is any single character, and the tiled (~) escapes the asterisk or question mark, which tells Google Sheets to interpret it literally. In the formula above, we look for any string of characters before “sun” using the * symbol.
Count the Number of Cells Without Certain Text Anywhere in the Cell
Lastly, let’s count all cells except those containing the word sun anywhere in the cell.
We use wildcards again, but we place them before and after the text in this case.
Video Tutorial
Conclusion
Between these three techniques, you should be able to find a way to count cells that do not contain certain strings of text.