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.
Contents
Count Using The COUNTIF Function
Now let’s look at using the COUNTIF function.
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.
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 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.