To compare one text with another in Excel, use Logical Operators. If one text is not equal to another in Excel, use the “*Not Equal t*o” operator.

Examples are shown below:

## Introduction to “Not Equal to” Operator in Excel

The ** Not Equal to **operator is used for comparing two values. Its function is opposite to the

*Equal**(*

*=**)*operator. Excel takes a pair of angle brackets (

**<>**) as the

**operator. It returns a Boolean expression either**

*Not Equal to***TRUE**(when not equal to) or

**FALSE**(when equal to).

### Method 1 – Compare a Text with Another Using “Not Equal to” Operator Directly

To compare **Text 1 **and** Text 2** from this dataset:

⏩ **Steps**:

- Enter the following formula for comparing cells
**B5**and**C5**.

`=B5<>C5`

- Press
**ENTER**and the cell will return**TRUE**as the text values of these two cells don’t match. - Drag the
**Fill Handle**tool downward to Autofill the formula below.

- The Boolean result will be shown after comparing all the text values.

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

⏩ **Steps**:

- Apply the following formula to match cell
**B5**with**C5**.

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

- Press
**ENTER**and copy the formula down to the other cells. If the text values match, Excel will return “**Matched**” else “**Not Matched**”.

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

To calculate the total price from this dataset excluding the item: **Mobile**.

⏩ **Steps**:

- Enter the formula in cell
**C12**.

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

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

- It will return the total price of the products excluding
**Mobile**(i.e.**18000**).

**Method 4 – **Using “Not Equal to” with COUNTIF and COUNITFS Functions

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

⏩ **Steps**:

- Select a cell and add the formula below.

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

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

- Press
**ENTER**to count the number of items excluding**Mobile**.

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

To count the items without “**Mobile**” and “**TV**”:

⏩ **Steps**:

- Apply the following formula in a selected cell.

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

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

- Press
**ENTER**to count the total cells excluding**Mobile**and**TV**.

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

To find the average sales of **Male **and **Female **Sales reps respectively from the given dataset:

⏩ **Steps**:

- Apply the following formula to get the average sales by the
**Male**sales reps.

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

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

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.

- To calculate the average of
**Female**sales reps, apply the formula below.

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

## Things to Remember

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

** Download Practice Workbook**

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