How to Sum Negative and Positive Numbers in Excel - 2 Methods

To sum positive and negative numbers in the dataset:

Method 1 – Sum Only Positive Numbers

1.1 Using SUMIF Function

Sum all numbers above 0 using a condition with the SUMIF function. The syntax of the SUMIF function is:

SUMIF(Cell Range,”Condition”)

Steps:

Select the cell to see the sum. Here, D12.

Select F6 and enter the following formula.

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

It will show the sum of all positive numbers in B5:D10. “>0” is the condition that defines the numbers.

Press Enter. The sum of all positive numbers will be displayed.

1.2 Applying a VBA Code

Steps:

Go to the Developer and select Visual Basic. The Microsoft Visual Basic for Application window will open. You can also press Alt+F11 to open it.

Click Insert and select Module.

Enter the following code in the Module.

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

Press Ctrl+S to save the code. Save the workbook as XLSM or Macro-Enabled Excel Workbook.

Select the range to find the sum of positive numbers. Here, (B5:D10).

Go to the Developer tab and click Macros.

In the Macro dialog box, select sum_positive to see the sum of positive numbers and click Run.

Select the output cell and click OK. Here, D12.

The sum of all positive values is displayed in our data range.

Method 2 – Sum Negative Numbers Only in Excel

2.1 Using SUMIF Function

The procedure is exactly the same as the one used for positive numbers.

Select D12 and enter the following formula to sum all negative numbers.

=SUMIF(B5:D10,”<0”)

The sum of all positive numbers is displayed in B5:D10. Here “>0” is the condition.

Press Enter to see the result.

2.2 Applying a VBA Code

Follow the procedure described in 1.2.

Use this code.

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

Save the code. A new Macro is created (sum_negative()).

Run the function to see the result:.

Things to Remember

With the SUMIF function, it is better to select the text range manually.
The VBA method is recommended while working with large ranges and different equations.

How to Sum Negative and Positive Numbers in Excel – 2 Methods