How to Sum Negative and Positive Numbers in Excel

Get FREE Advanced Excel Exercises with Solutions!

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.

=SUMIF(B5:D10,”>0″)

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.

Easy Methods to Sum Negative and Positive Numbers in Excel

  • After writing the equation, we will press Enter. That will show us the sum of all positive numbers.

Easy Methods to Sum Negative and Positive Numbers in Excel

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

Easy Methods to Sum Negative and Positive Numbers in Excel

  • 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).

Easy Methods to Sum Negative and Positive Numbers in Excel

  • 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.

Easy Methods to Sum Negative and Positive Numbers in Excel

  • 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.

Easy Methods to Sum Negative and Positive Numbers in Excel

  • Finally, we will see the sum of all the positive values in our data range.

Easy Methods to Sum Negative and Positive Numbers in Excel

Read More: How to Change Positive Numbers to Negative in Excel (6 Ways)


Similar Readings


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.

=SUMIF(B5:D10,”<0”)
This will show the sum of all the positive numbers in the range B5 to D10. Here “>0” is the conditional part implying the fact that the numbers that will be added should be less than 0.

Easy Methods to Sum Negative and Positive Numbers in Excel

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

Easy Methods to Sum Negative and Positive Numbers in Excel

  • Then saving the code will give us a new Macro named sum_negative().

  • Finally, running this function will give results like the following.

Easy Methods to Sum Negative and Positive Numbers in Excel

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.


Related Articles

Nasir Muhammad Munim

Nasir Muhammad Munim

I am Nasir Muhammad Munim from Dhaka, Bangladesh. I work as an Excel and VBA Content Developer for Exceldemy right now. Electrical and electronic engineering was my major at Islamic University of Technology, where I got my degree. I love creative works and learning new things. And I'm really into online games, especially first-person shooter games.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo