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 way 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


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 a 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


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

In this section, you will find 5 unique and effective ways to use the “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


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 down to get the cells matched. If the text values match, Excel will return “Matched” else “Not Matched”.

Result of IF function


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


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


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 calculate the corresponding sales values from the range D5:D11 and calculate 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


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.

📁 Download Practice Workbook


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.


<< Go Back to Text | If Cell Contains | Formula List | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Rafiul Hasan
Rafiul Hasan

Rafiul Hasan, holding a BSc in Naval Architecture and Marine Engineering from Bangladesh University of Engineering & Technology, contributes significantly to the ExcelDemy project with almost 1.6 years of dedicated work. Currently an Excel and VBA Content Developer, he has a passion for problem-solving. Authoring over 100 articles for ExcelDemy showcases expertise in Microsoft Office Suites and Data Analysis. In addition to content development, Rafiul actively engages with the ExcelDemy forum, offering valuable solutions to user queries and... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo