How to Use 'Not Equal To' in Excel

‘Not equal to’ operator (<>) is one of the six logical operators available in Microsoft Excel, which helps check if one value is not equal to another. It is also known as a Boolean operator because the resulting output of any calculation with this operator can only be either TRUE or FALSE.

The <> is a comparison operator that compares two values. If the values are NOT equal, it will return TRUE; otherwise, it will return FALSE. The Not Equal operator is often used along with other conditional functions, such as IF, OR, SUMIF, and COUNTIF functions to create formulas. Now let’s see how we can use ‘Not Equal to’ in Excel.

How to Use the ‘Not Equal to’ `<>` Comparison Operator in Excel

The syntax of ‘Not Equal’ is:

=[value_1]<>[value_2]

value_1 – the first value to be compared.
value_2 – the second compared value.

Let’s see how the <> operator works in Excel with some formulas and examples.

Example formula:

=A5<>B5

As you can see below, the formula in cell C5 returns TRUE because the value in cell A5 is not equal to the value in cell B5.

Here, the formula in cell C6 returns FALSE because the value in cell A6 is equal to the value in cell B6.

Let’s see how the ‘Not Equal to’ operator works with text values. It works the same way as it does with the number value.

Remember ‘Not Equal to’ operator in Excel is ‘case-insensitive’, which means even if the values are in different text cases, case differences will be ignored as shown below.

Using ‘<>’ Operator with Functions

Now that we’ve learned how the ‘not equal’ operator works, let’s see how to effectively combine it in other functions.

Using ‘Not Equal To’ with IF Function in Excel

The <> operator is very useful on its own, but it becomes more useful when combined with an IF function. The IF function checks whether certain conditions are met and in case that they are, it returns a certain result, else it returns another result.

The syntax for the IF function is:

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

Let’s assume we have an inventory list, which lists products and their quantities. If a product’s stock goes below 100, we need to restock it.

Use the below formula:

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

The formula above checks if the quantity of a product (C2) is not equal to 100, if it’s any less than hundred, then it returns ‘Restock’ in cell D2; if quantity is equal to 100, then it returns ‘Full stock’.

Now, drag the fill handle to apply the formula to other cells.

Using ‘Not Equal To’ with COUNTIF Function in Excel

Excel COUNTIF function counts the cells that meet a given condition in a range. If you want to count the number of cells with a value not equal to the specified value, enter COUNTIF with the ‘<>’ operator.

=COUNTIF(range,criteria)

The criteria used in COUNTIF are logical conditions that support logical operators (>,<,<>,=).

Let’s say we have a student’s marks list. And we want to count the number of students who have passed the test. Below is the formula used:

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

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

; otherwise,

The SUMIF function is used to sum all the numbers when adjacent cells match a certain condition in a range. The general structure of SUMIF function is:

=SUMIF(range,criteria,[sum_range])

In the example below, we want to find the total number of fruits ordered that are not mango. We can use the <> operator with SUMIF function to sum all values from the range (B2:B17) whose adjacent cells (A2:A17) are not equal to ‘Mango’. The result is 144 (cell E2).