### 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
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
```

**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 _
(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.

`=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
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
```

**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.

## 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:

`=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.
- 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

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