# VBA to sum all positive values in column, and all negative

#### espenskeie

##### Well-known Member
Hello

I have a column with some positive and some negative values, and also some empty cells.

I try to figure out how to write a VBA code for this, and would prefer to avoid .formula codes if possible...

This is what I have been messing with so far, completely without success:
Code:
``````Sub TEst(rRange As Range)
rRange As Double
Application.Volatile True
Dim rSumRange As Range
Dim tosum As Long

Set rSumRange = .Range("AD2", .Cells(Rows.count, 31).End(xlUp))

For Each rSumRange In rRange
With rSumRange
If IsNumeric(.Value) Then
If .Value > 0 Then
tosum = tosum + .Value
End If
End If
End With
Next rSumRange
End Sub``````

Regards
Espen

### Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
Hi Espen,

I think it might be easiest to use SUMIF here e.g. for the positive numbers:
Excel Workbook
AB
116
22
33
4-1
5-2
Sheet1
Excel 2010
Cell Formulas
RangeFormula
B1=SUMIF(A1:A5,">0")
And the equivalent VBA code would be something like this (I have used a message box just as a demonstration):

Code:
``MsgBox WorksheetFunction.SumIf(rSumRange, ">0")``

You don't need .Formula, but the worksheet function SUMIF can already do this, which you can use in your VBA code. Do you want to avoid this as well?

I was most worried about using .formula functions because the data they will refer to will change every now and then, and then those values will change too.

But I guess I can solve that by running a copy of each section after its done, and paste special with paste values?

Regards
Espen

Here's a way without using worksheet functions:

Code:
``````Sub test()
Dim x As Long, c As Range, positiveSum As Double, negativeSum As Double
If IsNumeric(c) Then
If Sgn(c) = 1 Then positiveSum = positiveSum + c
If Sgn(c) = -1 Then negativeSum = negativeSum + c
End If
Next
Range("AD" & x + 1) = "Positive: " & positiveSum
Range("AD" & x + 2) = "Negative: " & negativeSum
End Sub``````

You don't need .Formula to work with worksheet functions in VBA. (Note, not all Excel functions are available this way)

Here is the same thing using worksheet functions.

Code:
``````Sub test()
Dim c As Range, positiveSum As Double, negativeSum As Double
positiveSum = Application.WorksheetFunction.SumIf(c, ">0")
negativeSum = Application.WorksheetFunction.SumIf(c, "<0")
c.Offset(2).Resize(1) = "Positive: " & positiveSum
c.Offset(3).Resize(1) = "Negative: " & negativeSum
End Sub``````

Thanks HOTPEPPER, the one without worksheet functions worked just perfect.

May I ask if it is difficult to add an extra criterea? Now it does sum based on positive or negative values. But if I like to have 4 groups, in the column besides, column AE, I have values like "Long" and "Short".

If thats possible I would save some space and maybe a code or two...

Regards
Espen

You mean, you have in Column AD, the words Long and Short, and all positive values in Column AE?

Replies
3
Views
101
Replies
2
Views
175
Replies
3
Views
458
Replies
5
Views
108
Replies
12
Views
642

### Forum statistics

1,203,387
Messages
6,055,122
Members
444,763
Latest member
Jaapaap ### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?    1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option. Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com". Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back