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.

Share Button
Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , ,

Leave a Reply

Your email address will not be published.

*