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