Excel SUMIFS Not Equal to Multiple Criteria (4 Examples)

Get FREE Advanced Excel Exercises with Solutions!

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.

Sample Dataset


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.

Use the SUMIFS Function with Multiple Not Equal to Criteria

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.

Use the SUMIFS Function to Cells Not Equal to Multiple Criteria Partially

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


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.

Apply the SUMIFS Function with Multiple Not Equal to Criteria with Cell Reference

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,"<>")

Sum the Non Blank Cells

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

Tanjim Reza
Tanjim Reza

Hello! I am Md. Tanjim Reza Tanim. I have just completed my B.Sc from Naval Architecture & Marine Engineering Department, BUET. Currently, I am working as an Excel & VBA content developer. I always had a great fascination with Microsoft Excel and its cool functions and formulas. Here, I am learning every day about new functions and formulas and working on applying MS Excel to the analysis of our real-life problems. I have great enthusiasm for learning any kind of new things, writing articles, and solving real-life problems.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo