COUNTIF Function with “Not Equal to” Criterion in Google Sheets

Suppose you are working on a dataset where you need to find out the values which are not equal to the blank cell or a particular cell you are looking for. But finding every single data manually is nearly impossible if the dataset is large. Also if you manage to find the required data which are not equal to the desired data then this process is very time-consuming. Moreover, this process is quite easy if you use the COUNTIF function. In this article, we will learn how to use the COUNTIF function for not equal to criterion in google sheets.

Here is the overview of this process. You will learn more once you go through the whole article.

A Sample of Practice Spreadsheet

You may copy the spreadsheet below and practice by yourself.

2 Practical Examples of Using COUNTIF Function with “Not Equal to” Criterion in Google Sheets

The dataset below contains State Name, Temperature, and Status. Basically, this dataset represents the temperature status of different states. Now, we will calculate which values are not equal to the desired values using different methods. So, let’s start.

1. Applying COUNTIF Function with “Not Equal to” Criterion for Single String

Here, we will use the COUNTIF function not equal to a single string with the following dataset. Say, our target is to count the number of occurrences where State Name is not equal to California. Follow the steps below to accomplish this.

📌 Steps:

• First, select cell G5 to enter the formula.

• Then, enter the COUNTIF function to get the values with are not equal to the result value.

• After that select lookup range B5:B11 as below.

• Finally, write down the desired value. Here the value is California and enclose the value with double quotes.

• The output value 4 represents that the dataset has four values that are not equal to California.

Read More: How to Use COUNTIF for Cells Not Equal to Text in Google Sheets

2. Executing COUNTIF Formula with “Not Equal to” Criterion for Blank Cell

Moreover, we will calculate the COUNTIF function for finding occurrences not equal to the blank cell with another dataset. Follow the steps below to accomplish this.

📌 Steps:

• In the beginning, select cell G4 to enter the formula as before.

• After that, enter the COUNTIF function and enter the lookup range C5:C11 as below.

• In the end, write (<>) which represents the not equal to sign, and enclose it with the double quote as it represents the blank cell.

• Consequently, output 5 represents that 5 values are not blank here.

Read More: Google Sheets Count Cells from Another Workbook with COUNTIF Function

How to Count Cells with “Not Equal to” Criterion for Multiple Strings in Google Sheets

Now, we will use the COUNTIFS function to calculate the values not-equal to multiple strings with the first dataset shown before. Here, we want to count the number of occurrences for both the State Name not equal to California and the Status not equal to Cold.

📌 Steps:

• Initially, select cell G4 to enter the formula.

• Consequently, enter the COUNTIFS function to get the not equal value from desired values.

• Therefore, select the first lookup range B5:B11 to get the match from this column.

• Write down the desired value from column B and enclose the value with a double quote.

• Now, select another lookup range D5:D11 as below.

• Finally, write down the desired value from column D and enclose it with double quotes.

• Finally, the output shows 3. That means this dataset has 3 cells that neither contain California nor Cold.

Read More: Use COUNTIF If Cell Contains Specific Text in Google Sheets

Things to Remember

• While working on multiple columns, the output will only show equal if all the values in all the columns are equal.
• Enclose your search values with double quotes to complete your formula.

Conclusion

In this article, we explained how to use the COUNTIF function for not equal to criterion in Google Sheets with practical examples. Hopefully, the methods will help you apply this formula to your dataset. Please let us know in the comment section if you have any further queries or suggestions. You may also visit our OfficeWheel blog to explore more Google Sheets-related articles.

Related Articles

• Google Sheets Add Calculated Field for Pivot Table with COUNTIF
• How to Use COUNTIF for True Condition in Google Sheets
• [Fixed!] COUNTIF Function Is Not Working in Google Sheets
• Use COUNTIF Function to Count Checkbox in Google Sheets
• How to Use COUNTIF Function with OR Logic in Google Sheets
• COUNTIF with Greater than and Less than Criteria in Google Sheets
• How to Use VLOOKUP with COUNTIF Function in Google Sheets