Count Cells Without Certain Text Anywhere in the Cell

There are many 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.

First, we will be using COUNTIF, which is the most efficient method of conditional counting as it combines an IF statement with the COUNT function. Second, we will use QUERY, which is more powerful but difficult to learn.

💡 COUNTIF is not case-sensitive. QUERY is case-sensitive.

Count Using The COUNTIF Function

Now let’s look at using the COUNTIF function.

Using COUNTIF to count cells containing text anywhere in the string
Cells Not Containing “Sun” or “sun”

We use wildcards to match any characters before or after the word sun,. 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 below, we allow any string of characters before or after “sun” using the * symbol.

=COUNTIF(A1:A5,"<>*SUN*")

The COUNTIF function is not case-sensitive, so it does not count the text “Sun” at the end of cell A3 even though the S is capitalized. Next, we will use QUERY, which is case-sensitive.

Count Using The QUERY Function

Secondly, we’ll bring the QUERY function into the mix as a possible solution.

Using QUERY to count cells containing text anywhere in the string
Cells Not Containing “Sun”

The query function is the most powerful and flexible function in Google Sheets. However, its syntax differs from other spreadsheet functions, so it can take a while to learn.

=QUERY(A1:A5,"select count(A) where not A contains 'Sun'",-1)

Although the syntax may be unfamiliar, it makes sense if you read it aloud. Because the query function is case sensitive, it will only work for Sun and not sunsUn, etc. Learn about the syntax of the query function here.

Video Tutorial

Video thumbnail
YouTube Tutorial about Counting Text Without Certain Characters

Conclusion

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

Related Posts

Leave a Comment