In this article, we are going to discuss how to use the Excel **SUMIFS **function with Not Equal to Text criteria. Excel users often need to sum specific values from a dataset. To do so, they will need to apply some conditions. In this article, we will exclude those values which we do not want to sum by using not equal syntax in the **SUMIFS **function.

**Table of Contents**hide

## SUMIFS with Not Equal to Text Criteria in Excel: 4 Suitable Examples

Today, we are going to discuss **SUMIFS **not equal to text. Here, we have a dataset that contains the sales quantity of different tech products along with their delivery date. We are going to use the **SUMIFS **function to exclude some of the values based on some criteria and add the rest of the values.

### 1. SUMIFS Function for Not Equal to Partially Matched Text

In this instance, we are going to sum the sales quantity of products whose name does not contain the word “Phone” in it. This allows users to add the sales quantity of the laptops only.

We are going to use the following formula in the **C18 **cell to do so,

`=SUMIFS(E5:E14,C5:C14,"<>*Phone*")`

Now, if we hit **Enter **the formula will add the sales quantity of the laptops only.

**How the Formula Works:**

- The formula sums the values in the
**E5:E14** - However, before summing up the values it goes through the
**C5:C14**range and checks if the products’ names are not equal to “Phone” with the “<>*Phone*” syntax. - The “*” means partial match.
- So, the formula excludes “Ear Phone” and “Smart Phone” and sums only the sales values of laptops and the total sum is 1150.

Similarly, if you want you can use as many criteria as you want. We can use the following formula to address multiple criteria,

`=SUMIFS(E5:E14,C5:C14,"<>*Phone*",F5:F14,"<>02/05/2023")`

**How the Formula Works:**

The **SUMIFS** function adds up the values in the range **E5:E14** that meet multiple criteria specified in the subsequent arguments.

**E5:E14:**The first argument is the range of values to sum.**C5:C14:**The second argument is the range of values to test against the first criterion.**“<>*Phone*”:**The third argument is the criterion to apply to the first range, which is to include values in the range that do not contain the text “Phone”. The <> operator means “not equal to”, and the * characters are wildcards that allow any text before and after the word “Phone”.**F5:F14:**The fourth argument is the range of values to test against the second criterion.**“<>02/05/2023”:**The fifth argument is the criterion to apply to the second range, which is to include values in the range that are not equal to the date “02/05/2023”. The <> operator means “not equal to”.

**Read More: Excel SUMIF Function for Not Equal Criteria**

### 2. SUMIFS Function for Not Equal to Exactly Matched Text

In this example, we will add the values of the “Qunatity Sold” column that do not match either “Smart Phone” from the “Product” column and “04/05/2023” date from the “Delivary Date” column. We will insert the following formula in the **C18** cell to get the result,

`=SUMIFS(E5:E14,C5:C14,"<>Smart Phone",F5:F14,"<>04/05/2023")`

As we hit **Enter**, the formula will add the sales quantity of the products excluding “Smart Phone” and the products whose delivery date is “04/05/2023”.

**How the Formula Works:**

- The formula adds the sales quantity of the products from the
**E5:E14**range that are neither Smart Phone nor the delivery date of the products is “04/05/2023”.

**Read More: How to Use Excel SUMIF with Greater Than Criterion**

### 3. SUMIFS Function for Not Equal to Cell References of Texts

In this case, we will use the cell reference of criteria texts instead of hard coding them into the formula to sum the sales quantity from the **E5:E14** range based on that criteria. This will give us flexibility. As the formula is not hard coded the cell reference can be changed as we change the criteria. We will enter the following code into the **C18 **cell to do the task,

`=SUMIFS(E5:E14,D5:D14,"<>"&C16,C5:C14,"<>"&C17)`

As soon as we hit the **Enter** button, the formula will add up the values of the products which are not “Laptop” or whose brand is not “Asus”.

**How the Formula Works:**

- The formula adds the sales quantity of the products from the
**E5:E14**range whose company is not “Asus” or the products are not “Laptop”. **“<>”&C16:**This part concatenates the not equal to sign with the value in the**C16**cell which is “Asus” and the output is “<>Asus”. The “<>”& C17 portion does the same.

**Read More: How to Use Excel SUMIF with Blank Cells**

### 4. SUMIFS with Not Equal to Empty String

In this example, we will add the sales quantity of the products whose delivery dates are not blank. This will help us to get the total sales of the products whose delivery date is registered in our dataset. We will insert the following formula in the **C16** cell to do the task,

`=SUMIFS(E5:E14,F5:F14,"<>")`

As we hit **Enter**, the formula will add the sales quantity of the products whose delivery dates are not blank.

**How the Formula Works:**

The **SUMIFS** function adds up the values in the range **E5:E14** that meet a single criterion specified in the subsequent arguments.

**E5:E14: **The first argument is the range of values to sum.

**F5:F14:** The second argument is the range of values to test against the criterion.

**“<>”:** The third argument is the criterion to apply to the second range, which is to include values in the range that are not equal to an empty cell. The <> operator means “not equal to”.

In summary, the **SUMIFS** function is adding up the values in the range **E5:E14** that correspond to cells in the range** F5:F14** that are not empty.

**Read More: Sum If Greater Than and Less Than Cell Value in Excel**

**Similar Readings**

**How to Sum If Cell Contains Number and Text in Excel****How to Sum If Cell Contains Number in Excel****How to Use Excel SUMIF to Sum Values Greater Than 0****How to Use SUMIF to SUM Less Than 0 in Excel**

**Download Practice Workbook**

You can download the practice book here.

## Conclusion

In this article, we have talked about **SUMIFS** not equal to text. This article will help users to truncate any values from the dataset and then sum them as they desire. This will also help them to understand the **SUMIFS **function properly.