Excel SUMIF Function for Not Equal Criteria

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:

Arguments:

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.

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

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

Read More: Excel SUMIFS with Not Equal to Text Criteria

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.

• 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?

• Firstly, press Alt + F11 to open the Visual Basic editor. Then select Insert >> Module. You can also select the Visual Basic editor from the ribbon. To do so, you need to display the Developer tab on the ribbon.
• Then, 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

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.
• Press Alt + F11 to open the Visual Basic editor. Then select Insert >> Module.
• 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 _
(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“.
• 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.

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

Read More: How to Use Excel SUMIF with Blank Cells

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.
• Press Alt + F11 to open the Visual Basic editor. Then select Insert >> 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
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

Read More: How to Use SUMIF Function to Sum Not Blank Cells in Excel

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.

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.

Related Articles

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

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