# 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.
`=SUMIF(E5:E17, "<>250",F5:F17)`
• 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.
`=SUMIF(E5:E17, "<>Navada",F5:F17)`
• 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.
`=SUMIF(D5:D17, "<>Navada",F5:F17)+SUMIF(E5:E17, "<>250",F5:F17)`
• 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
Set Criteria_Range = Range("D5:D17")
sumResult = Application.WorksheetFunction.SumIf(Criteria_Range, criteria, sumRange)
Worksheets("SUMIF_VBA").Range("C20") = sumResult
End Sub``````

Code Breakdown:

• The code first declares the necessary variables, sumRange to represent the range to sum, Criteria_Range to represent the range with criteria, criteria to hold the “not equal” criteria, and sumResult to store the calculated sum.
• The sumRange and Criteria_Range variables are set to the appropriate ranges in your worksheet.
• The criteria variable is set to “<>Navada”, which specifies the “not equal to Navada” condition. Modify these criteria to fit your specific needs.
• The sumResult variable is calculated using the Application.WorksheetFunction.SumIf method, which applies the SUMIF function with the specified criteria.
• Finally, the calculated sum (sumResult) is assigned to cell C20 in the “SUMIF_VBA” worksheet using the Worksheets(“SUMIF_VBA”).Range(“C20”) = sumResult statement.
• You will see the SUMIF result in cell C20.

### 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 _
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.
`=SUMIFS(F5:F17,D5:D17,"<>Navada",E5:E17,"<>250")`
• 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
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``````

Code Breakdown:

• The code begins with the Sub declaration for the subroutine named “SUMIFS_VBA“.
• It then declares the necessary variables, including sumRange to represent the range to sum, Criteria_Range1 and Criteria_Range2 to represent the ranges with criteria, criteria1 and criteria2 to hold the “not equal” criteria, and sumResult to store the calculated sum.
• The ranges and criteria variables are set to the appropriate values in your worksheet.
• The sumResult variable is calculated using the Application.WorksheetFunction.SumIfs method, which applies the SUMIFS function with the specified criteria.
• The calculated sum (sumResult) is assigned to cell C20 in the “SUMIFS_VBA” worksheet using the Worksheets(“SUMIFS_VBA”).Range(“C20”) = sumResult statement.
• See the SUMIFS result for not equal criteria in the worksheet.

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:

`=SUMIF(range, "<>value", sum_range)`

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.
• Make sure to adjust the worksheet name and range references in the VBA code to match your actual workbook structure.

## Related Articles

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

Get FREE Advanced Excel Exercises with Solutions!
Mehedi Hasan Shimul

Md. Mehedi Hasan, with a BSc in Electrical & Electronic Engineering from Bangladesh University of Engineering and Technology, holds a crucial position as an Excel & VBA Content Developer at ExcelDemy. Driven by a deep passion for research and innovation, he actively immerses himself in Excel. In his role, Mehedi not only skillfully addresses complex challenges but also exhibits enthusiasm and expertise in gracefully navigating tough situations, emphasizing his steadfast commitment to consistently deliver exceptional and quality content.... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF