In this tutorial, I will show you amazing way to Sum only Positive or Negative Numbers in Excel.When you have range of data which contains both positive and negative numbers then you will need SUMIF function to Make your task easy.
Explanation Of Formula
The Excel SUMIF function which will be used looks like the following:
=SUMIF(range, criteria, [sum_range])
The SUMIF function syntax has the following arguments:
- range Required. The range of cells that you want evaluated by criteria. Cells in each range must be numbers or names, arrays, or references that contain numbers. Blank and text values are ignored. The selected range may contain dates in standard Excel format
- criteria Required. The criteria in the form of a number, expression, a cell reference, text, or a function that defines which cells will be added. Wildcard characters can be included – a question mark (?) to match any single character, an asterisk (*) to match any sequence of characters. If you want to find an actual question mark or asterisk, type a tilde (~) preceding the character.
- sum_range Optional. The actual cells to add, if you want to add cells other than those specified in the range argument. If the sum_range argument is omitted, Excel adds the cells that are specified in the range argument (the same cells to which the criteria is applied).
Now I will show you how you can use this function to sum positive and negative numbers by using Example in Excel.
Step 1:Below Example contains Data in Two columns A and B.Column A contains Date and Column B contains some Positive Values and Negative Values in which Positive Value=Profit and Negative value=Loss.
Step 2:Enter Following Formula in Total profit cell(For calculating Profit)
=SUMIF(B2:B8,”>0”,B2:B8) Note: Criteria “>0” for Addition of positive Numbers
Step3: Press Enter and Profit is calculated.
Step 4:Enter Following Formula in Total loss cell(For calculating Loss)
==SUMIF(B2:B8,”<0”,B2:B8)
Note: Use “<0”for Addition of Negative Numbers
Step 5: Press Enter and Loss is Calculated.
Conclusion
In this post we looked at SUMIF function used to add Positive or negative Numbers with Profit & Loss Example. Please leave a comment below If you have any questions or suggestions.I will reach out to you asap.Thank you!
