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.
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
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
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.
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, etc. Learn about the syntax of the query function here.
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.