Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

‘Not Equal to’ Operator in Excel (With 5 Examples)

In Microsoft Excel, there are many logical operators. We basically use these operators to perform a comparison between two or more values. One of them is the “Not Equal To (<>)” operator. In this tutorial, you will learn about the Not Equal To operator in Excel with appropriate examples and proper illustrations. So, read the whole article to enrich your Excel knowledge.

not equal to in excel

The above screenshot is an overview of the article which represents a simplified application of the Not Equal To operator in Excel. You’ll learn more about the dataset as well as the methods and functions under different criteria in the following sections of this article.


Download Practice Workbook

Download this practice workbook.


What is ‘Not Equal to’ in Excel?

The Not Equal To is a logical operator that helps compare two values. It is opposite to the Equal To. In simple terms, we use this Not Equal To operator to make sure a cell or value is not the same as another specified value. To express this operator, we use the pair of angle brackets (<>) in Excel. It returns a boolean value TRUE or FALSE.

  • TRUE means the two values are not identical or equal.
  • FALSE means the two values are the same or equivalent.

How to Use ‘Not Equal to’ Operator in Excel?

Now, the basic syntax of this operator is like following:

=value1 <> value2

The Values can be anything, it may be text, numerical values or you can use the cell references here.

Look at the following examples:

not equal to in excel

Here you can see wherever the values are not equal, it returns TRUE. On the other hand, if the values are equivalent, it returns FALSE. So, this is the primary usage of the Not Equal To operator in Excel.


5 Examples of ‘Not Equal to’ Operator with Other Functions

In the following section, we are providing you with five suitable and effective examples that you can implement into your dataset. Make sure you check them all. We recommend you learn and apply all these. It will surely enrich your Excel knowledge.


1. ‘Not Equal to’ in IF Function

Here, we are combining the Not Equal To operator with the IF function in Excel. Now, you can use the Not Equal To operator as a condition in the IF function. Then, it will return the result based on this.

Now, take a look at the following dataset:

Here, we have some students’ names and their grades. Now, our goal is to find the students who failed the exam. If anyone’s grade is not equal to “F”, they are passed.

📌 Steps

  • First, type the following formula in Cell D5:

=IF(C5<>"F","Passed","Failed")

not equal to in excel

  • Then, press Enter.
  • After that, drag the Fill handle icon over the range of cells D6:D9. It will copy the formula all over these cells.

not equal to in excel

As you can see, we have successfully used the Not Equal To operator with the IF function in Excel.

Read More: How to Use Greater Than or Equal to Operator in Excel Formula


2. ‘Not Equal To’ with Excel SUMIF Function

In this example, we are combining the Not Equal To operator with the SUMIF function in Excel. The SUMIF function adds the cells specified by a given condition or criteria. Here, we are using the Not Equal To operator as criteria.

Take a look at the following dataset:

Here, you can see some salespersons’ names, their selling products, and the sales amount. Now, our goal is to find total sales without the product TV.

📌 Steps

  • First, type the following formula in Cell C12:

=SUMIF(C5:C9,"<>"&C11,D5:D9)

‘Not Equal To’ with Excel SUMIF Function

  • After that, press Enter.

Here you can see the total sales without the product TV. In the end, we successfully used the Not Equal To operator with the SUMIF function Excel.

Read More: Excel Boolean Operators: How to Use Them?


3. Excel COUNTIF Function with ‘Not Equal To’ Operator

Now, in this example, we will combine the COUNTIF function with the Not  Equal To operator in Excel. The COUNTIF function counts the number of cells within a range that meets the given condition.

Take a look at the following dataset:

Here, we have a dataset of some Fruits. Now, our goal is to count the number of total fruits without including “Apple”.

📌 Steps

  • First, type the following formula in Cell D5:

=COUNTIF(B5:B13,"<>"&"Apple")

Excel COUNTIF Function with ‘Not Equal To’ Operator

  • After that, press Enter.

As you can see, we have a total of five fruits without “Apple”. In this way, you can use the Not Equal To operator with the COUNTIF function in Excel.

Read More: How to Use Less Than Or Equal to Operator in Excel (8 Examples)


4. Combining ‘Not Equal To’ with COUNTIFS Function

This example is similar to the previous one. The COUNTIFS function counts the number of cells in one or more given arrays that maintain one or more specific criteria. As it contains conditions, we can use multiple Not Equal To operators.

Now, take a look at the following dataset:

Here, we have a dataset of some colors. Now, we are going to use the Not Equal To operator to count the number of colors without “Red” and “Blue”.

📌 Steps

  • First, type the following formula in Cell D5:

=COUNTIFS(B5:B13,"<>"&"Red",B5:B13,"<>"&"Blue")

  • After that, press Enter.


5. AVERAGEIF with ‘Not Equal To’ in Excel

Now, you can also mix it up with the AVERAGEIF function in Excel. The AVERAGEIF function finds the average for the cells specified by a given condition or criteria. We are going to use this with our Not Equal To operator.

Take a look at the following dataset:

In this dataset, we have some data on some persons’ salaries. Now, our goal is to find the average salary of both “Male” and “Female”.

📌 Steps

  • First, type the following formula in Cell D12:

=AVERAGEIF(C5:C10,"<>"&"Female",D5:D10)

  • After that, press Enter.

AVERAGEIF with ‘Not Equal To’ in Excel

  • Again, type the following formula in Cell D13:

=AVERAGEIF(C5:C10,"<>"&"Male",D5:D10)

AVERAGEIF with ‘Not Equal To’ in Excel

  • After that, press Enter.

As you can see, we have calculated the average salary of “Male” and “Female” with the help of the AVERAGEIF function and Not Equal To operator.


💬 Things to Remember

Not Equal To operator requires at least two values to check. It won’t work until you give two values.

Make sure your conditions are correct while using the Not Equal To operator.

Not Equal To operator is case-insensitive. For instance, if you compare “ball” and “BALL” in, it will return FALSE.


Conclusion

To conclude, I hope this tutorial has provided you with a piece of useful knowledge to use the Not Equal To operator in Excel. We recommend you learn and apply all these instructions to your dataset. Download the practice workbook and try these yourself. Also, feel free to give feedback in the comment section. Your valuable feedback keeps us motivated to create tutorials like this.

Don’t forget to check our website ExcelDemy.com for various Excel-related problems and solutions.

Keep learning new methods and keep growing!


Further Readings

Shanto

Shanto

Hello! I am Shanto. An Excel & VBA Content Developer. My goal is to provide our readers with great tutorials on various Excel-related problems. I hope our easy but effective tutorials will enrich your knowledge. I have completed my BSc in Computer Science & Engineering from Daffodil International University. Working with data was always my passion. Love to work with data, analyze those, and find patterns. Also, love to research. Always look for challenges to keep me growing.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo