Count Cells That Don’t End With 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.

First, we will use COUNTIF in the first example, 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.

Formula used: =COUNTIF(A1:A5,"<>*sun*")

We want to count all values that do not end in the word “sun.” The combination of the < and > operators tell Google Sheets to count everything except what comes after those operators.

To accomplish this, we need to accept any characters before the word sun. We use wildcards to accomplish this. 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 shown below, we allow any string of characters before “sun” using the * symbol.

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

Count Using The QUERY Function

Next, let’s bring the QUERY function into the mix as a possible solution.

Formula used: =QUERY(A1:A5, "select count(A) where not A ends with 'Sun'", -1)

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.

Although the syntax may be unfamiliar, it makes sense when you read it aloud. It counts all cells that don’t (not) end with ‘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 text strings. Choose which one makes the most sense for your situation.

Related Posts