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.
Leave a Reply