There are a lot of different operators available that can be used while putting formulas in Microsoft Excel. One such operator about which we are going to talk is the not equal to <> operator.

This operator checks whether the two values provided by the user are not equal to each other. You can also combine the operator with conditional functions if you want to automate data calculations.

The Not equal to operator (<>) belongs to the family of six logical operators that are available in Microsoft Excel, this helps in comparing the two values if they are equal or not.

The not equal to the operator is also called a Boolean operator as the resulting output of any of the calculations that are done using the Not equal to operator can only be either true or false.

The not equal to has a symbol of <> and it is a comparison operator which helps in comparing two values. If the two values aren’t equal then it will return TRUE, else it will return FALSE.

Most of the people who use Not equal to operator in Microsoft Excel, they use it with conditional functions like IF, OR, SUMIF, and COUNTIF functions to create formulas. Below we have discussed how you can use the Not Equal to operator in Excel.

How To Use the Not Equal To <> Comparison Operator in Excel

The syntax of the Not Equal to operator is:

=[value_1]<>[value_2]

• Value_1 – The first value that needs to be compared
• Value_2 – The second value with which the first one will be compared.

Example

=A5<>B5

1. In the below screenshot we can see that the formula in cell C5 is returning TRUE because the value in cell A5 is not equal to the value in cell B5.
   
2. Now, you can see the formula in cell C6 returns FALSE. This is because the value in cell A6 is equal to the value in cell B6.
   
3. Here we will try comparing the text values using the Not Equal to operator. It should work the same way as it works with the numbers.
4. The Not Equal to operator is case sensitive- which means that if you compare ‘A and a’, it will show False.
   

Using ‘<>’ Operator with Functions

Now, let’s discuss how to use the Not Equal to operator and effectively combine it with other functions to make it a formula.

Using Not Equal to with IF Function in Excel

The <> Not Equal to operator comes in handy quite often. It is a very powerful and useful operator, and we see its true potential when it is combined with the IF FUnctions. The IF function can check whether certain conditions are met and in case they are, then it returns a certain result, otherwise, it can return another result.

The syntax for the IF function with not equal to operator is-

=IF(logical_test,[value_if_true],[value_if_false])

Let’s take an example where we have a list of 100 items, If the list of items goes below 100, then we need to restock it. We can use the formula below to execute the same-

=IF(C2<>100,"Restock","Full stock")

The above-mentioned formula will be able to check the quantity of a product (C2) which is not equal to 100, if it is less than 100, then it will return the answer as Restock in cell D2. Otherwise, if the quantity is equal to or above 100 then it will return Full Stock.

You can drag the fill handle to apply the same formula to other cells.

Using Not Equal to with COUNTIF Function in Excel

Another function of Excel is the COUNTIF Function which counts the cells that meets a given condition in a range of cells. So, if you want to count the number of cells with a value which isn’t equal to a specified value or number, then you can enter the formula COUNTIF with the <> operator.

=COUNTIF(range,criteria)

The criteria that are used in the COUNTIF are logical conditions that support logical operators (>,<,<>,+). Let’s take another example to learn the COUNTIF function and its use. So, we have a student’s marks list. And we want to count the number of students who have passed the test. We can use the formula below to find the answer.

=COUNTIF(C2:C9,"<>FAIL")

The formula counts cells C2 to C9 and if the value is NOT ‘FAIL’. Then the result is displayed in cell C11.

Using Not Equal to with SUMIF Function in Excel

The SUMIF function can be used to sum all the numbers when the adjacent cells match a specific condition in a range of cells. The syntax for the SUMIF function in Excel is-

=SUMIF(range,criteria,[sum_range])

Another example is below to explain the use of the SUMIF function. If we want to find the total number of fruits ordered that are not mango then we can use the <> operator along with the SUMIF function to sum all the values from the range (B2:B17) and the adjacent cells are (A2:A17). Since they are not equal to Mango. The result is 144 in cell E2. Below is the formula that we will use-

=SUMIF(A2:A17,"<>Mango",B2:B17)

That’s it, this was all about the Not Equal To operator <> in Microsoft Excel.