# Excel SUMIF Function for Not Equal Criteria

Get FREE Advanced Excel Exercises with Solutions!

In this article we will know how to use Excel SUMIF not equal criteria. Excel’s SUMIF function is a strong tool for computing sums based on predetermined criteria. You can quickly take particular values out of your calculations by using the “not equal to” condition. This article will examine using the SUMIF function for not equal criteria in Excel and provide examples that you can use to streamline your data summarization operations. By perfecting the use of Excel SUMIF not equal criteria, you can improve your Excel abilities and streamline your calculations. Let’s get going! ## Introduction to Excel SUMIF Function

Summary:

This function adds the cells specified by a given condition or criteria.

Syntax:

`=SUMIF (range, criteria, [sum_range])`

Arguments:

ARGUMENT REQUIRED/OPTIONAL EXPLANATION
range Required The range of cells that we want to be evaluated by criteria.
criteria Required The criteria are in the form of an expression a number, a text, a function, or a cell reference, that defines which cells are to add.
sum range Optional The actual cells to add if we need to combine cells other than those defined in the range argument.

Versions:

Excel 2007 to onwards.

Note:

• In criteria, you can include wildcard characters – An asterisk (*) is used to match any string of characters, and a question mark (?) is used to match any single character. Like six?”, “apple*,” and “*?”
• Here, the question mark (?) will be used for matching any single character.
• An asterisk (*) will be used to match any sequence of characters. Using this method, we can find out any text or string by matching any substring. For “*Apples” we can find the words like Pineapples or any other words where the last portion is “Apples”.
• sum_range should be the same size and shape as the range.
• SUMIF function only supports a single condition.

## Excel SUMIF Not Equal Criteria: 5 Suitable Examples

Now in this section, we will discuss the use of the Excel SUMIF function for not equal criteria. In the examples, we will use different Excel formulas and also VBA codes. We have used a dataset that consists sales report for a company with Employee ID, Name, State, Sales Unit, and Sales columns. ### 1. Calculating Sum with Numeric Not Equal Single Criteria

In the first example, we will use the SUMIF function for single numeric not equal criteria. Without further delay let’s hover over the procedures.

• Simply, use the following formula in a cell of your worksheet.
`=SUMIF(E5:E17, "<>250",F5:F17)`
• Simultaneously, you will see the sum of Sales whose Sales Unit is not 250 will be calculated. In the formula, E5:E17 is the criteria range, “<>250” is the not equal criteria and F5:F17 is the sum range for the SUMIF function.

### 2. Finding Sum with Text Criteria Using Excel SUMIF Function

This time, we will use a similar fashioned formula to use SUMIF for not equal criteria. Except, the criteria are in text format this time. Without swinging here and there, let’s jump to the procedures.

• Simply, use the following formula in a cell of your worksheet.
`=SUMIF(E5:E17, "<>Navada",F5:F17)`
• As a result, you will see 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.

### 3. Evaluating Sum with Multiple Not Equal Criteria Applying SUMIF Function

Now, we will discuss the use of the SUMIF function for multiple not equal criteria. Actually, it’s not possible to use SUMIF for multiple criteria. But we can use two SUMIF formulas with two different criteria and sum their result. Technically, this will use SUMIF for multiple not equal criteria.

• Simply, use the following formula in a cell of your worksheet.
`=SUMIF(D5:D17, "<>Navada",F5:F17)+SUMIF(E5:E17, "<>250",F5:F17)`
• Simultaneously, you will 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.

### 4. Applying SUMIF for Not Equal Criteria with Excel VBA

We all know, 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?

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.
• Instantly, you will see the SUMIF result in cell C20. ### 5. Use of VBA to Insert SUMIF Formula in Worksheet

In another way, we can use the VBA, let’s use the VBA to insert the SUMIF formula for not equal criteria in the worksheet. It’s a concise piece of code.

• Firstly, you have to Open VBA Window & Insert New Module. If you don’t know how to open VBA yet.
• Then, 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“.
• Consecutively, we will see SUMIF results for not equal criteria. ## How to Use SUMIFS Function for Not Equal Criteria

Now, let’s see the use of the SUMIFS function to sum for not equal criteria. The formula and procedure is quite similar. Let’s do it.

• Simply, use the following formula in a cell of your worksheet.
`=SUMIFS(F5:F17,D5:D17,"<>Navada",E5:E17,"<>250")`
• Simultaneously, you will see 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

Why not use the VBA to use the SUMIFS for not equal criteria? Let’s hover over the procedures.

• Firstly, you have to Open VBA Window & Insert New Module.
• Then, 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.
• Finally, the calculated sum (sumResult) is assigned to cell C20 in the “SUMIFS_VBA” worksheet using the Worksheets(“SUMIFS_VBA”).Range(“C20”) = sumResult statement.
• As a result, we will 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.

## Conclusion

Using the “not equal to” criteria with the SUMIF function in Excel provides a powerful tool for data summation and analysis. By excluding specific values from the sum calculation, you can obtain more precise results and streamline your calculations. Throughout this article, we explored the syntax and usage of Excel SUMIF not equal criteria, providing practical examples and addressing common questions. By mastering this technique, you can enhance your Excel skills and become more efficient in handling data. If you have any queries or suggestions, feel free to leave comments. Mehedi Hasan Shimul

Hi! I am Mehedi Hasan Shimul. As I am an Engineer solving different problems with the help of Excel amuses me. I write Excel related different problem solving articles here. Hope it will help you.

We will be happy to hear your thoughts Advanced Excel Exercises with Solutions PDF  