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 use COUNTIF, 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 complex.

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

Count Using The COUNTIF Function

Now let’s look at using the COUNTIF function.

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

The first parameter for COUNTIF is the range. We are looking at the cells starting in A1 and going through A5 so we will specify A1:A5 as the range.

The second parameter for the COUNTIF function is the criterion for which we will be searching. We want to count any cells that don’t contain the string SUN.

To do this, 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 above, we allow any string of characters before or after “sun” by using the * symbols as such *SUN*.

We then tell Sheets that we want anything but these values by adding the greater than and less than signs <>*SUN* before the string.

The COUNTIF function is not case-sensitive, so it counts 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.

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

The data parameter tells the function where its source data is. Let’s use A1:A5 since that’s where our list of words lies.

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

Next, we write the QUERY in the SQL language. Although the syntax may be unfamiliar, it makes sense if you read it aloud. We are selecting the counts of column A where they don’t have the word “Sun.”

Because the query function is case-sensitive, it will only work for Sun and not sun, sUn, etc. Learn about the syntax of the query function here.

Video Tutorial

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 that will work with your spreadsheet.

Related Posts