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.
📁 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).
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.
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
- 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.
- Hence, you will get the Boolean result after comparing all the text values.
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")
- 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”.
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.
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)
- After that, you will get the total price of the products excluding Mobile (i.e. 18000)
Read More: Sum If a Cell Contains Text in Excel (6 Suitable Formulas)
Similar Readings
- How to Use VLOOKUP If Cell Contains a Word within Text in Excel
- Assign Value If Cell Contains Word in Excel (4 Easy Ways)
- If Cell Contains Text Then Return Value in Another Cell Using Excel Formula
- How to Return Value in Excel If Cell Contains Text from List
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
- Then, pressing ENTER will count the number of items without considering Mobile.
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
- Next, pressing ENTER will count the total cells without considering Mobile and TV.
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.
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
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)
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
- If Cell Contains Text Then Add Text in Another Cell in Excel
- How to Use IF-THEN Statements with Text in Excel (7 Examples)
- Highlight Cell If Contains Text in Excel (with Easy Steps)
- If Cell Contains Text Then Copy to Another Sheet in Excel
- Check If Cell Contains Partial Text in Excel (5 Ways)
- How to Check If Cell Contains Text Then Return Value in Excel
- If Cell Contains Specific Text Then Add 1 in Excel (5 Examples)