Tom’s Tutorials For Excel: Summing Only Positive or Negative Numbers

Tom’s Tutorials For Excel: Summing Only Positive or Negative Numbers

In a list that contains positive and negative numbers, here are formulas that can sum those numbers in different ways, depending on the nature of your project.

Based on the picture:

• Formula of only positive numbers summed: =SUMIF(B3:B15,">0")

• Formula of only negative numbers summed: =SUMIF(B3:B15,"<0")

• Formula of all numbers summed as if positive: =SUM(ABS(B3:B15))
Note, this last formula is an array formula. Recall, an array formula is applied to a cell by simultaneously pressing the Ctrl+Shift+Enter keys, not just Enter. The curly braces are not typed in by you; Excel places them automatically when the array formula is properly applied.

If you are unfamiliar with array formulas, see my video and explanation of arrays here.