Count Cells Not Containing Certain Text – Google Sheets

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.

Count the Number of Cells Without Certain Text (Exact Match)

First, let’s start with the simplest example.

Count cells if they don't have the word "Sunny"
All Cells Except Those With Sunny

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.

Count cells that don't end with certain text
Cells Not Ending in “Sun”

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.

Counting cells that don't have the word sun
Counting if “Sun” is Not Anywhere in the Cell

We use wildcards again, but we place them before and after the text in this case.

Video Tutorial

YouTube player


Between these three techniques, you should be able to find a way to count cells that do not contain certain strings of text.