In this tutorial, you’ll learn how to use the Excel SUMIF() function to sum values that aren’t equal to a particular value or values. The SUMIF() function is used to add values based on a condition. Because of this, we can use the function to sum values that aren’t equal to a value. Similarly, we can use the Excel SUMIFS() function to add values not equal to multiple values.
By the end of this tutorial, you’ll have learned the following:
- How to use the Excel SUMIF() function to add values not equal to a value
- How to use the Excel SUMIFS() function to add values not equal to multiple values
How to Use Excel SUMIF() to Add Values Not Equal to a Value
To use the Excel SUMIF() function to add values not equal to a particular value, you can pass that value in an as condition using the “<>” (not equal to) operator.
Let’s break this down a little further. The Excel SUMIF() function is used to add values conditionally. The function accepts up to three arguments:
- The criteria range,
- The criteria to meet, and
- The sum range
Because of this, we can sum values based on a condition in another column. For example, in the use case below, we have a dataset that contains regional sales. Say that we want to add up the sales values based on a value not being equal to a particular value in the region range. The SUMIF() function allows this, since it allows us to pass in the optional sum range argument.
Let’s take a look at what this looks like in Excel:
In the example above, we’re using the following formula:
=SUMIF(B3:B13, "<>North", C3:C13)This formula instructs Excel to search for the criteria in the range B3:B13. We’re using the condition of "<>North", which indicates that we’re looking for values that aren’t equal to North. Keep in mind that this criteria argument is case-insensitive, meaning that it will also find cells equal to north and other permutations. Finally, we use the C3:C13 range as the sum range argument, which instructs Excel to add only the values that meet the condition.
In the following section, you’ll learn how to use the Excel SUMIFS() function to add values not equal to multiple values.
How to Use Excel SUMIFS() to Add Values Not Equal to Multiple Values
The Excel SUMIFS() function can be used to add values based on multiple conditions being met. Because of this, we can use the function to add values that aren’t equal to multiple values.
Let’s take a look at the Excel SUMIFS() function and its arguments to better understand how to use it:
=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)We can see that the function has two required arguments and up to 254 optional arguments. Let’s take a look at these arguments in more detail:
| Argument | Description | Required? |
|---|---|---|
sum_range= | The range of cells you want to sum up | Yes |
criteria_range1= | The range of cells to look for a condition in | Yes |
criteria1 | The criteria to use | Yes |
| [criteria_range2, criteria2] | The additional pairs of criteria and criteria ranges to look for | No |
Now that we know that we can pass in multiple ranges of criteria, let’s take a look at an example. We’ll use the same dataset we used earlier and only sum values that are not equal to North or South.
In the example above, we used the following formula:
=SUMIFS(C3:C13,B3:B13, "<>North", B3:B13, "<>South")Notice that the ordering of the arguments is different in the SUMIFS() function: we place the sum range as the first positional argument.
In the code block above, we are passed in the sum range covering our sales data. For the first condition range and condition, we re-use the arguments from the first example. By using the "<>North" condition, we are searching for cells that aren’t equal to North. As our second condition, then, we passed in the same range with the criteria of "<>South".
When we hit the return key, the function returns only the sum of the values where the region is not equal to either North or South. Remember that the function ignores casing, meaning that the values will be ignored even if the casing is different.
Conclusion
In this tutorial, you learned how to use the Excel SUMIF() function and SUMIFS() function to add cells not equal to a value. This allows you to easily add values conditionally. You first learned how to use the SUMIF() function for a single condition. Then, you used the SUMIFS() function for checking multiple conditions. Being able to do this using formulas allows you to keep the integrity of your data intact, without needing to worry about filtering data.
Additional Resources
To learn more about related topics, check out the resources below: