# 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.

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.

## 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.