Excel SUMIF Not Equal Criteria: 5 Suitable Examples

Method 1 – Calculating Sum with Numeric Not Equal Single Criteria

• Use the following formula in a cell of your worksheet.

• The sum of Sales whose Sales Unit is not 250 will be calculated.

E5:E17 is the criteria range, “<>250” is the not equal criteria and F5:F17 is the sum range for the SUMIF function.

Method 2 – Finding Sum with Text Criteria Using Excel SUMIF Function

• Use the following formula in a cell of your worksheet.

• The sum of Sales whose State is not Navada will be calculated.

Note: Everything is the same in the formula as in method 1 except the criteria is text this time.

Method 3 – Evaluating Sum with Multiple Not Equal Criteria Applying SUMIF Function

• Use the following formula in a cell of your worksheet.

• See the sum of Sales whose Sales Unit is not 250 and State is not Navada will be calculated separately, then added together.

In the formula, we used two SUMIF formula and added the result.

Method 4 – Applying SUMIF for Not Equal Criteria with Excel VBA

VBA (Visual Basics for Application) code can do almost all tasks of Excel. So why not use it to sum with SUMIF for not equal criteria?

• Press Alt + F11 to open the Visual Basic editor. Then select Insert >> Module. You can also select the Visual Basic editor from the ribbon. You need to display the Developer tab on the ribbon.
• Write the following code in the Module and run it.

Code:

Sub SUMIF_VBA()
Dim sumRange As Range
Dim Criteria_Range As Range
Dim criteria As Variant
Dim sumResult As Double
Set sumRange = Range("F5:F17") ' Replace with your desired range
criteria = "<>Navada" ' Replace with your desired "not equal" criteria
Set Criteria_Range = Range("D5:D17")
sumResult = Application.WorksheetFunction.SumIf(Criteria_Range, criteria, sumRange)
Worksheets("SUMIF_VBA").Range("C20") = sumResult
End Sub

Method 5 – Use of VBA to Insert SUMIF Formula in Worksheet

• You have to Open VBA Window & Insert New Module.
• Press Alt + F11 to open the Visual Basic editor. Then select Insert >> Module.
• Write the following code in the module and run it.

Code:

Sub SUMIF_VBA_Formula()
Worksheets("SUMIF_VBA_Formula").Range("C20").Value = Application.WorksheetFunction.SumIf _
(Range("D5:D17"), "<>Navada", Range("F5:F17"))
End Sub

In the code,

• the code starts with the Sub declaration for the subroutine named “SUMIF_VBA_Formula“.
• It then sets the value of cell C20 in the “SUMIF_VBA_Formula” worksheet using the Worksheets(“SUMIF_VBA_Formula”).Range(“C20”).Value statement.
• The value being assigned is the result of the Application.WorksheetFunction.SumIf function.
• The SumIf function is used to sum the values in the range F5:F17 based on the criteria that the corresponding values in the range D5:D17 are not equal to “Navada“.
• See SUMIF results for not equal criteria.

How to Use SUMIFS Function for Not Equal Criteria

• Use the following formula in a cell of your worksheet.

• The sum of Sales whose Sales Unit is not 250 and State is not Navada will be calculated.

In the formula, the sum of values in the range F5:F17 is calculated based on the following criteria:

• The corresponding values in the range D5:D17 should not equal “Navada“.
• The corresponding values in the range E5:E17 should not equal 250.

How to Use SUMIFS Function for Not Equal Criteria with VBA

• Open VBA Window & Insert New Module.
• Press Alt + F11 to open the Visual Basic editor. Select Insert >> Module.
• Write the following code in the module and run it.

Code:

Sub SUMIFS_VBA()
Dim sumRange As Range
Dim Criteria_Range1 As Range
Dim Criteria_Range2 As Range
Dim criteria1 As Variant
Dim criteria2 As Variant
Dim sumResult As Double
Set sumRange = Range("F5:F17") ' Replace with your desired range
criteria1 = "<>Navada" ' Replace with your desired "not equal" criteria
Set Criteria_Range1 = Range("D5:D17")
criteria2 = "<>250"
Set Criteria_Range2 = Range("E5:E17")
sumResult = Application.WorksheetFunction.SumIfs _
(sumRange, Criteria_Range1, criteria1, Criteria_Range2, criteria2)
Worksheets("SUMIFS_VBA").Range("C20") = sumResult
End Sub

Frequently Asked Questions

1. How does the “not equal to” criteria work with the SUMIF function?

Ans: The “not equal to” criteria, represented by the “<>” operator, allows you to exclude specific values from the sum calculation. When using the SUMIF function with the “not equal to” criteria, you can specify a value that should not be included in the sum.

2. What is the syntax for using “not equal to” with SUMIF?

Ans: The syntax for using the “not equal to” criteria with the SUMIF function is as follows:

Here, “range” represents the range of cells to evaluate, “<>value” specifies the value to exclude, and “sum_range” is the range of cells to sum.

3. Are there any alternative approaches to achieve similar results?

Ans: Yes, you can achieve similar results by using alternative functions like SUMIFS or by combining multiple SUMIF functions with logical operators. Additionally, Excel’s array formulas can also be utilized for more complex scenarios involving multiple criteria.

Things to Remember

• Don’t forget to save the file as the xlsm file before running any code.
• Be careful about the cell references in the formulas.
• Download our practice workbook for better clarification.
• Make sure to adjust the worksheet name and range references in the VBA code to match your actual workbook structure.

Download Practice Workbook

You can download the practice workbook from here.

Related Articles

• Excel SUMIF Not Working
• [Fixed!] Excel SUMIF with Wildcard Not Working

<< Go Back to Excel SUMIF Function | Excel Functions | Learn Excel