Excel is extremely handy for large and complex calculations. SUMIFS function is a function to sum definite ranges depending on definite conditions. Sometimes, you might need to sum some cells that are not equal to definite values. In this article, I will show you how to perform SUMIFS cells not equal to multiple criteria.
SUMIFS Not Equal to Multiple Criteria: 4 Simple Uses
Say, we have a dataset of different products’ sales quantity for the individual months of the first quarter of a year. Now, we want to sum the sales quantities depending on multiple criteria where the cells are not equal to certain values. We can do this by using the SUMIFS function with cells not equal to multiple criteria.
Example 1: Use SUMIFS for ‘Not Equal to’ Criteria with Multiple Text
Suppose, you want to sum the sales quantities for the month of January except for Carrot and Root. You can accomplish this by using the SUMIFS function for cells not equal to these multiple criteria. Follow the steps below to achieve this target.
📌 Steps:
- First and foremost, click on the G7 cell and insert the following formula.
=SUMIFS(C5:C16,B5:B16,"<>Carrot",B5:B16,"<>Root")
- Subsequently, hit the Enter button.
Thus you see that all the sales quantities of the January month are summed up and shown except for Carrot and Root’s sale quantity.
Read More: How to Use SUMIFS When Cells Are Not Equal to Multiple Text
Example 2: Sum for Data Partially Not Matching a Text
Now, say you want to sum the sales quantities for the month of January for products except any Chocolate item and Root. You can accomplish this by following the steps below.
📌 Steps:
- At the very beginning, click on the G7 cell.
- Following, write the formula below in the formula bar.
=SUMIFS(C5:C16,B5:B16,"<>*Chocolate*",B5:B16,"<>Root")
- Subsequently, press the Enter button.
As a result, you can sum the sales quantities for the month of January except for any chocolate item and Root.
Read More: Excel SUMIFS with Multiple Sum Ranges and Multiple Criteria
Similar Readings
- Excel SUMIFS with Multiple Vertical and Horizontal Criteria
- SUMIFS with INDEX-MATCH Formula Including Multiple Criteria
- How to Apply SUMIFS with INDEX MATCH for Multiple Columns and Rows
Example 3: Apply ‘Not Equal to’ Criteria Concatenated with Cell Reference in SUMIFS Function
Now, if you want to use cell references to apply SUMIFS to cells not equal to Carrot and Root, you can go through the steps below.
📌 Steps:
- Initially, click on the G7 cell.
- Afterward, insert the formula below.
=SUMIFS(C5:C16,B5:B16,"<>"&B5,B5:B16,"<>"&B7,B5:B16,"<>"&B13)
- Subsequently, press the Enter button.
Thus, you can get all the sales quantities sum without the Carrot and Root items.
Example 4: Apply Excel SUMIFS to ‘Not Equal to Blank’ Criteria
Now, you might need to sum the cells that are not blank. In this regard, you can follow the steps below.
📌 Steps:
- First, click on the G7 cell.
- Subsequently, insert the formula below and hit the Enter button.
=SUMIFS(C5:C16,B5:B16,"<>")
Thus, you can get all the sales quantity sum without the blank cells in the Product column.
Read More: How to Use VBA Sumifs with Multiple Criteria in Same Column
Download Practice Workbook
You can download our free practice workbook from here!
Conclusion
So, in this article, I have shown you 4 suitable examples of Excel SUMIFS with multiple not equal criteria. I suggest you read the full article carefully and practice accordingly. I hope you find this article helpful and informative. Besides, you are very welcome to comment here if you have any further questions or recommendations.
Related Articles
- How to Use SUMIFS with Multiple Criteria in the Same Column
- Exclude Multiple Criteria in Same Column with SUMIFS Function
- How to Apply SUMIFS with Multiple Criteria in Different Columns
- SUMIFS with Multiple Criteria Along Column and Row in Excel
- How to Use SUMIFS Function with Wildcard in Excel
- How to Use SUMIFS Function with Multiple Sheets in Excel
- [Fixed]: SUMIFS Not Working with Multiple Criteria