How to Perform SUMIFS with “Not Equal to” Multiple Criteria (4 Examples)

Example 1: Using SUMIFS for “Not Equal to” Criteria with Multiple Text

Suppose you have a dataset containing sales quantities for different products during the first quarter of the year. You want to sum the sales quantities based on multiple criteria, excluding specific values (such as “Carrot” and “Root”). Follow these steps:

  • Click on cell G7.
  • Enter the following formula in the formula bar:
=SUMIFS(C5:C16,B5:B16,"<>Carrot",B5:B16,"<>Root")
  • Press Enter.

Use the SUMIFS Function with Multiple Not Equal to Criteria

This will give you the total sales quantity for January, excluding the quantities for Carrot and Root.

Read More: How to Use SUMIFS When Cells Are Not Equal to Multiple Text


Example 2: Summing Data Partially Not Matching a Text

Suppose you want to sum the sales quantities for January, excluding any chocolate items and “Root.” Follow these steps:

  • Click on cell G7.
  • Enter the formula below in the formula bar:
=SUMIFS(C5:C16,B5:B16,"<>*Chocolate*",B5:B16,"<>Root")
  • Press Enter.

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

This will give you the total sales quantity for January, excluding chocolate items and Root.

Read More: [Fixed]: SUMIFS Not Working with Multiple Criteria


Example 3: Applying “Not Equal to” Criteria with Cell References in SUMIFS

If you prefer using cell references to apply SUMIFS for cells not equal to Carrot and Root, follow these steps:

  • Click on cell G7.
  • Enter the formula below:
=SUMIFS(C5:C16,B5:B16,"<>"&B5,B5:B16,"<>"&B7,B5:B16,"<>"&B13)
  • Press Enter.

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

This will give you the total sales quantity without including Carrot and Root.


Example 4: Applying Excel SUMIFS to “Not Equal to Blank” Criteria

To sum the sales quantities without including blank cells in the Product column, follow these steps:

  • Click on cell G7.
  • Enter the formula below:
=SUMIFS(C5:C16,B5:B16,"<>")
  • Press Enter.

Sum the Non Blank Cells

This will give you the total sales quantity, excluding any blank cells in the Product column.

Read More: SUMIFS with INDEX-MATCH Formula Including Multiple Criteria


Download Practice Workbook

You can download the practice workbook from here:


Related Articles


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

Get FREE Advanced Excel Exercises with Solutions!
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