Nasir Muhammad Munim
One of the core features of Excel is to show the sum of numbers. It is widely used in different sectors. Summing positive numbers is fairly easy but if there are positive and negative mixed in the dataset it is quite tough to sum only positive or negative numbers separately. So in this article, we will show how to sum negative and positive numbers in Excel using 2 different methods.
Download Practice Workbook
You can download the practice workbook from here.
2 Easy Ways to Sum Negative and Positive Numbers in Excel
There are two different ways by which we can sum negative and positive numbers in excel. One is using Excel-defined functions for conditional approach and another one is using VBA code to sum negative and positive numbers. For clarifying both of these methods, we will consider the following dataset. Here we simply want to sum the positive and negative numbers.
1. Sum Only Positive Numbers in Excel
1.1 Using SUMIF Function
All we need to do is sum all the numbers if they are above 0 value or below 0 value. So we can use it as a condition that we will add numbers only when they are above 0 value, or below 0 value. The SUMIF function does exactly that. It adds the numbers only when the condition is satisfied. The syntax of the SUMIF function is:
SUMIF(Cell Range,”Condition”)
The steps to sum negative and positive numbers in excel using the SUMIFÂ function are below.
Steps:
- First, we need to select the cell where we will show the sum of positive and negative numbers. In our case, we want to see the sum of all positive numbers in cell D12.
- Second, we will select the F6 cell and write the following equation in the formula bar.
This will show the sum of all the positive numbers in the range B5 to D10. You can change the cell range according to your need. Here “>0” is the conditional part that defines the numbers that will be added should be greater than 0.
- After writing the equation, we will press Enter. That will show us the sum of all positive numbers.
Read More: How to Apply Formula for Positive and Negative Numbers in Excel
1.2 Applying VBA Code
When we are doing a large amount of calculations, we often use VBA code to reduce the excessive work and runtime. So in this method, we will learn how to sum negative and positive numbers using VBA code. The steps are shown below.
Steps:
- At first, we will go to the Developer tab in the Ribbon and select Visual Basic. A new window will appear named Microsoft Visual Basic for Application. We can do the same by pressing Alt+F11 as well.
- Next, we will click on Insert and select Module. A writing panel will appear.
- Then we will write the following code in the writing panel.
Sub sum_positive()
Dim work_s As Worksheet
Dim rg As Range
Dim output As Range
Set work_s = Application.ActiveSheet
Set rg = Application.Selection
Set output = Application.InputBox("Select the Cell Where You Want to See Output", "Select the Cell", Type:=8)
output.Value = Application.WorksheetFunction.SumIf(rg, ">0")
End Sub
- Furthermore, we will press Ctrl+S to save the code. We will save the workbook with XLSM extension or Macro-Enabled Excel Workbook.
- After that, we will select the range of cells where we want to find the sum of negative and positive numbers. In our case, the range is (B5:D10).
- Again we will go to the Developer tab and Click on Macros.
- So this will trigger the Macro dialog box. We will select sum_positive to show the sum of positive numbers and click on Run.
- As a result, a small selection box will appear asking for the cell location where we want to see the output or the sum. Select the desired output cell and click on OK. In our case, the desired cell is D12.
- Finally, we will see the sum of all the positive values in our data range.
Read More: How to Change Positive Numbers to Negative in Excel (6 Ways)
Similar Readings
- How to Put a Negative Number in Excel Formula (4 Easy Methods)
- Excel Formula to Return Zero If Negative Value is Found
- How to Show Negative Numbers in Excel (4 Quick Tricks)
- Excel Negative Numbers in Brackets and Red (2 Examples)
- How to Move Negative Sign at End to Left of a Number in Excel
2. Sum Only Negative Numbers in Excel
2.1 Using SUMIF Function
The procedures are exactly the same as summing only positive numbers. The only difference is we will select the cell D12 and write the following equation to sum all the negative numbers.
After pressing Enter the result will be like below.
Read More: How to Add Negative Numbers in Excel (4 Easy Ways)
2.2 Applying VBA Code
The procedures are the same as before for this method as well. The changes are below:
- The code for this method is below.
Sub sum_negative()
Dim work_s As Worksheet
Dim rg As Range
Dim output As Range
Set work_s = Application.ActiveSheet
Set rg = Application.Selection
Set output = Application.InputBox("Select the Cell Where You Want to See Output", "Select the Cell", Type:=8)
output.Value = Application.WorksheetFunction.SumIf(rg, "<0")
End Sub
- Then saving the code will give us a new Macro named sum_negative().
- Finally, running this function will give results like the following.
Read More: [Fixed!] Excel Not Adding Negative Numbers Correctly (4 Solutions)
Things to Remember
- For the SUMIF function, it is better to select the text range manually instead of writing if there are multiple areas we need to select.
- The VBA method is recommended while working with large ranges and different equations. Otherwise, the SUMIF function is suitable for most of the cases.
- The entire demonstration was done in Excel 365. So the interface may vary with different versions, especially the older versions.
Conclusion
If you’re still having trouble to sum positive and negative numbers in excel, let us know in the comments. Our team is ready to answer all of your questions. For any excel related problems, you can visit our website Exceldemy for all types of excel related problem solutions.
