Sum of positive or negitive values only from a list

[wallyxl]

Is there a quick way to get the sum of only positive or negative numbers.
I have 21 columns, some cells will be positive and some will be negative, but none will be zero.
In column 22 I would like the sum of all the positive numbers and in column 23 the sum of all the negative.

Thank you.

 

[MARK858]

Positive

=SUMIF(A2:U2,">0")

Negative

=SUMIF(A2:U2,"<0")

Change ranges to suit and drag down

 

[wallyxl]

Hi Mark,
Thanks for that, works great.
If anyone else is using this and you want to turn the negative to a positive just add x-1 at the end of the formula.

 

[MARK858]

If anyone else is using this and you want to turn the negative to a positive just add x-1 at the end of the formula.

Another way...

=ABS(SUMIF(A2:U2,"<0"))

and a macro if you want it...

Sub SumRng()
    Range("V2:V" & Range("A" & Rows.Count).End(xlUp).Row).FormulaR1C1 = "=SUMIF(RC[-21]:RC[-1],"">0"")"
    Range("W2:W" & Range("A" & Rows.Count).End(xlUp).Row).FormulaR1C1 = "=ABS(SUMIF(RC[-22]:RC[-2],""<0""))"
    With Range("V2:W" & Range("A" & Rows.Count).End(xlUp).Row)
        .Value = .Value
    End With
End Sub