How to Sum Only Positive (or Negative) Numbers in Excel

The Excel SUMIF() function allows you to add values conditionally, including summing only positive (or negative) numbers. Because the function allows you to add values based on a condition, you can be confident that your values will add correctly. In this tutorial, you’ll learn how to use the SUMIF() function to sum only positive (or negative) numbers in Excel.

By the end of this tutorial, you’ll have learned the following:

• How to use the Excel SUMIF() function to add only positive numbers
• How to use the Excel SUMIF() function to add only negative numbers
• How to add values in Excel where values in another column are either positive or negative

Sum Only Positive Numbers with Excel SUMIF()

In this section, you’ll learn how to sum only positive numbers with the Excel SUMIF() function. Because the function can be used to add values conditionally, we can easily add a condition to the function to include only positive numbers. This can be incredibly helpful if you are trying to add only incomes over a transaction sheet that also includes spending.

Let’s see how we can write a formula to add only numbers above zero:

=SUMIF(range, ">0")

Now that you have the formula to work with, let’s see what this looks like in action by using a hands-on example:

In the example above, we’re adding together only positive numbers. Because the criteria range is the same as our sum range, we can safely omit the third optional argument. Because our condition excludes any negative values, we’re summing up only positive values within the range.

Sum Only Negative Numbers with Excel SUMIF()

Similar to using Excel to sum only positive values, we can use the SUMIF() function to add together only negative numbers.

Let’s take a look at a hands-on example of how to add only negative values in Excel:

In the example above, we’re using a formula to sum only negative values. Let’s break down what this formula does:

=SUMIF(C3:C13, "<0")

While the SUMIF() function accepts three parameters (the criteria range, the criteria, and the sum range), we can avoid using the third argument if it’s the same as the criteria range. Note that we are passing in the criteria as ">0". It’s important to wrap the criteria in double-quotes. Not doing this will raise an Excel #NUM! error.

In the following section, you’ll learn how to add numbers based on values in another column being positive.

Sum Numbers Based On Another Column Being Positive with Excel SUMIF()

In this section, you’ll learn how to use the Excel SUMIF() function to add values based on values in another column being positive. This can be helpful when you want to add values that only have, say, a positive net change.

In the example above, we’ll load a new dataset that includes a performance column in addition to the previous date and amount columns. In this example, we’ll want to add up only values where the performance column is positive, regardless of whether the amount column is positive or negative.

In the example above, we used the following formula to add values in the Amount column, only if the values in the Performance column were negative.

=SUMIF(C3:C13, ">0", D3:D13)

In this case, the criteria is applied to the values in column C, which contains the performance. In this scenario, we check whether the values are above 0. If they are, we sum up the values in column D, our Amount column.

In the final section below, you’ll learn how to calculate the inverse of this by adding values in one column only if values in another column are negative.

Sum Numbers Based On Another Column Being Negative with Excel SUMIF()

In this section, you’ll learn how to sum values only if values in another column are negative. This use case follows a similar method as the example above, except we’ll be using a criteria of "<0" instead.

With the formula below, we’ll evaluate if the values in column C are less than 0 (indicating they’re negative). If this is the case, they’re included in our conditional sum calculation:

=SUMIF(C3:C13, "<0", D3:D13)

Let’s see what this looks like in practice:

Note that we are wrapping our condition in double-quotes, so that it reads as "<0". Writing the condition without the quotes would result in an Excel #NUM error.

Conclusion

In this tutorial, you learned how to sum only positive or only negative values in Excel. You learned how to do this using the SUMIF() function, which allows you to easily calculate sums based on a condition. First, you learned how to sum only positive values, followed by an example of how to sum only negative values. From there, you learned how to add values in one column, based on the condition that values in another column are positive. Finally, you learned how to do the opposite of this: adding values in one column based on the condition that values in another column are negative.

Additional Resources

To learn more about related topics, check out the resources below:

• How to Use Excel SUMIF() with Less Than Conditions
• How to Use Excel SUMIF() with Greater Than Conditions
• How to Use Excel SUMIF() When Not Equal To Value
• Excel SUMIF() Function: Official Documentation