How to Use “Not Equal to” Operator for Text in Excel (5 Examples)

Get FREE Advanced Excel Exercises with Solutions!

When you want to compare one text with another in Excel, Logical Operators pave the ways for performing this task. If one text is not equal to another in Excel, you can use the “Not Equal to” operator. In this article, I will show you how to use this logical operator if one text is not equal to another in Excel.

See all the examples in this article at a glance in the following image.

Excel If Not Equal to Text


📁 Download Practice Workbook

You can download the practice book from the link below.


Introduction to “Not Equal to” Operator in Excel

The Not Equal to operator is a kind of Logical Operator used for comparing two values. Its function is opposite to the Equal (=) operator. Excel takes pair of angle brackets (<>) as the Not Equal to operator. It returns a Boolean expression either TRUE (when not equal to) or FALSE (when equal to).

Not Equal to Operator


5 Examples to Use “Not Equal to” Operator for Text in Excel

In this section, you will find 5 unique and effective ways to use “Not Equal to” operator in Excel. Let’s check the ways!

1. Compare a Text with Another Using “Not Equal to” Operator Directly

You can just compare two texts directly by using the “Not Equal to” operator. It will compare whether two texts are exactly the same or not and then return a Boolean result.

Sample Dataset

We will compare Text 1 and Text 2 from this dataset.

Steps:

  • First of all, type the following formula for comparing cells B5 and C5.

=B5<>C5

Apply formula with Not Equal to operator

  • Then, press ENTER and the cell will return TRUE as the text values of these two cells don’t match.
  • Now, drag the Fill Handle tool downward to Autofill the formula below.

Autofill formula

  • Hence, you will get the Boolean result after comparing all the text values.

Compare text in Excel If Not Equal to Text

Read More: How to Return TRUE If Cell Contains Text in Excel (8 Easy Ways)


2. Use of “Not Equal to” Operator in IF Function to Set a Logical Test

The logical operator can be used along with the IF function. The “Not Equal to” operator is set as the logical_test in the IF function in this case.

Steps:

  • Firstly, apply the following formula to match cell B5 with C5.

=IF(B5<>C5,"Matched","Not Matched")

IF Function with Not Equal to operator in Excel

  • Then, press ENTER and copy the formula to down to get the cells matched. If the text values match, Excel will return “Matched” else “Not Matched”.

Result of IF function

Read More: How to Use IF & OR Functions with Text in Excel (3 Examples)


3. Apply “Not Equal to” Logic in SUMIF Function to Get Sum Excluding a Text Set Beforehand

The SUMIF function adds the cells specified by a given condition or criteria. So, you can use the logical operator as the criterion and create a formula.

SUMIF Function with Not Equal to operator in Excel

Here. we have a dataset of some Items purchased by specific customers and their corresponding prices. Our aim is to calculate the total price from this dataset excluding the item: Mobile.

In order to do so, just proceed with the steps below.

Steps:

  • Type the following formula in cell C12.

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

Here,

  • C5:C9 = range
  • D5:D9 = sum range
  • “<>”&C11 = criteria (not equal to cell value of C11)

SUMIF Function

  • After that, you will get the total price of the products excluding Mobile (i.e. 18000)

Output of SUMIF Function

Read More: Sum If a Cell Contains Text in Excel (6 Suitable Formulas)


Similar Readings


4. Using “Not Equal to” with COUNTIF and COUNITFS Functions

4.1 Single “Not Equal to” Criterion (COUNTIF Function)

Let’s combine the “Not Equal to” operator with the COUNTIF function. The COUNTIF function counts the cells within a range that meets a condition or criteria. Consider the dataset of Method 3. We will now count the number of items excluding “Mobile”.

Steps:

  • First of all, select a cell and type the following formula in that cell.

=COUNTIF(C5:C11,"<>"&C13)

Here,

  • C5:C11 = range
  • “<>”&C13 = criteria

COUNTIF Function with Not Equal to Operator for comparing Text in Excel

  • Then, pressing ENTER will count the number of items without considering Mobile.

COUNTIF function for Text


4.2 Multiple Simultaneous “Not Equal to” Criteria (COUNTIFS Function)

The COUNTIFS function acts almost similar to the COUNTIF function. This function counts the number of cells in one or more given arrays that maintain one or more specific criteria. Let’s say, we want to count the items without “Mobile” and “TV”.

Steps:

  • Here, apply the following formula in a selected cell.

=COUNTIFS(C5:C11,"<>"&"Mobile",C5:C11,"<>"&"TV")

Here,

  • C5:C11 = criteria range
  • “<>”&”Mobile” = criteria 1
  • “<>”&”TV” = criteria 2

COUNTIFS in Excel If Not Equal to Text

  • Next, pressing ENTER will count the total cells without considering Mobile and TV.

Output

Read More: Check If Cell Contains Text Then Return Value with Multiple Conditions in Excel


5. Use of “Not Equal to” Criteria with AVERAGEIF Function to Get Average Except for an Item

We will now use the “Not Equal to” operator with the AVERAGEIF function which finds the average for the cells specified by a given condition or criteria.

Dataset for AVERAGEIF

From this dataset, let’s find the average sales of Male and Female Sales reps respectively.

Steps:

  • Here, apply the following formula and get the average sales by the Male sales rep.

=AVERAGEIF(C5:C11,"<>"&"Male",D5:D11)

Here,

  • C5:C11 = range
  • “<>”&”Male” = criteria
  • D5:D11 = average range

AVERAGEIF in Excel If Not Equal to Text

This formula will take the Male from the range C5:C11 and calculates the corresponding sales values from the range D5:D11 and calculates the average of the Male reps.

  • Similarly calculate the average of Female sales reps by applying the formula below.

=AVERAGEIF(C5:C11,"<>"&"Female",D5:D11)

AVERAGEIF Function

Read More: Find Text in Excel Range and Return Cell Reference (3 Ways)


Things to Remember

  • The “Not Equal to” operator is by nature, not case-sensitive. So, if you have the same letters with uppercase and lowercase (i.e. Rat and rat), it will return FALSE.
  • You need at least two variables to apply this operator.

Conclusion

In this article, I have shown 5 examples of using “Not Equal to” logic for text in Excel. Hope the article helped you. If you have any questions, or feedback regarding this article, please don’t forget to share them in the comment box. We, the ExcelDemy team are always responsive to your problems. See you!


Related Articles

Rafiul Hasan
Rafiul Hasan

Hey there! I am Md. Rafiul Hasan. Currently, I am working as an Excel & VBA content developer. I like new ideas and want to explore the field of innovation. Excel has saved our work time and made it easy for us to perform quick calculations. I am trying to make it easier for you to overcome the obstacles you face while working on Excel. Stay connected!

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo