Suppose you’re working with a dataset where you need to find values that are not equal to either an empty cell or a specific cell you’re looking for. However, finding each individual data is nearly impossible if the dataset is voluminous. Moreover, even if you manage to find the required data that is not equal to the desired data, this process is very time-consuming. But worry not, because there’s a simple solution – the COUNTIF function. In this article, we’ll learn how to use the COUNTIF function with the “not equal to” criterion in Google Sheets.
A Practical Example of a Spreadsheet
You can copy the spreadsheet below and practice along.
Two Practical Examples of Using the COUNTIF Function with the “Not Equal to” Criterion in Google Sheets
The dataset below contains the “State Name,” “Temperature,” and “Status.” Basically, this dataset represents the temperature status in different states. We’ll now calculate the values that are not equal to the desired values using different methods. So, let’s get started.
1. Applying the COUNTIF Function with the “Not Equal to” Criterion for a Single String
Here, we’ll use the COUNTIF function with the “not equal to” criterion with the following dataset. Let’s say our goal is to count the number of occurrences where the “State Name” is not equal to “California.” Follow the steps below to achieve that.
📌 Steps:
- First, select cell G5 to enter the formula.
- Then, enter the COUNTIF function to obtain the values that are not equal to the result value.
- Next, select the range B5:B11 as shown.
- Finally, note the desired value. Here, the value is “California” and surround it with double quotes.
The output value of 4 represents that the dataset contains four values that are not equal to “California.”
2. Running the COUNTIF Formula with the “Not Equal to” Criterion for an Empty Cell
Furthermore, we’ll calculate the COUNTIF function to find the occurrences that are not equal to an empty cell with another dataset. Follow the steps below to achieve that.
📌 Steps:
- Firstly, select cell G4 to enter the formula as before.
- Then, enter the COUNTIF function and enter the range C5:C11 as shown.
- Finally, write (<>) which represents the “not equal to” sign and surround it with double quotes as it represents the empty cell.
The output of 5 represents that 5 values are not empty here.
How to Count Cells with the “Not Equal to” Criterion for Multiple Strings in Google Sheets
Now, we’ll use the COUNTIFS function to calculate the values that are not equal to multiple strings with the first mentioned dataset. Here, we want to count the number of occurrences where the “State Name” is not equal to “California” and the “Status” is not equal to “Cold.”
📌 Steps:
- Initially, select cell G4 to enter the formula.
- Then, enter the COUNTIFS function to obtain the value not equal to the desired values.
- Consequently, select the first range B5:B11 to match in that column.
- Note the desired value from column B and surround it with double quotes.
- Now, select another range D5:D11 as shown.
- Finally, note the desired value from column D and surround it with double quotes.
Finally, the output displays 3. This means that this dataset contains 3 cells that neither contain “California” nor “Cold.”
Key Takeaways
- When working with multiple columns, the output will only display “equal” if all values across all columns are equal.
- Surround your search values with double quotes to complete your formula.
Conclusion
In this article, we’ve explained how to use the COUNTIF function with the “not equal to” criterion in Google Sheets using practical examples. We hope these methods will help you apply this formula to your dataset. If you have any questions or additional suggestions, please let us know in the comments section. You can also visit our website Crawlan.com to explore more articles related to Google Sheets.
