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: [Fixed]: SUMIFS Not Working with Multiple Criteria


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: SUMIFS with INDEX-MATCH Formula Including Multiple Criteria


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


<< Go Back to Excel SUMIFS with Multiple Criteria | Excel SUMIFS Function | Excel Functions | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Tanjim Reza
Tanjim Reza

Md. Tanjim Reza Tanim, a BUET graduate in Naval Architecture & Marine Engineering, contributed over one and a half years to the ExcelDemy project. As an Excel & VBA Content Developer, he authored 100+ articles and, as Team Leader, reviewed 150+ articles. Tanim, leading research, ensures top-notch content on MS Excel features, formulas, solutions, tips, and tricks. His expertise spans Microsoft Office Suites, Automating Finance Templates, VBA, Python, and Developing Excel Applications, showcasing a multifaceted commitment to the... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo