Sum only positive or negative numbers

  • Thread starter Thread starter MAX258
  • Start date Start date
M

MAX258

I have one column - M1:M68
I want to sum all positive numbers in the column in one cell
and then
I want to sum all negative numbers in the column in another cell

I have tried multiple sumifs and sumproducts.

Thank you in advance,
Ma
 
Max,

Positive

=SUM((M1:M68>0)*(M1:M68))

Negative

=SUM((M1:M68<0)*(M1:M68))

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Max,

Sorry, I forgot to mention that they are array formula, so you commit with
Ctrl-Shift-Enter, not just Enter.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Max

Looking at Bob's formula, I reckon you'll need to array-enter it. That is
instead of pressing Enter when you've typed it (or edited it) press Ctrl
Shift and Enter. The alternative is to use the same formula but use
SUMPRODUCT instead of SUM

Andy.
 
Bob/Andy

Thanks again...but sill not working....is it possible my numbers aren'
formatted correctly or something like that
 
O.K. guys I got it....

it is =sumif(M1:M68,"<0",M1:M68)

Had to enter the <0, or >0 in quotes " "


Thanks again for all of your help.
Ma
 
Max

A couple of things to check. Make sure that you do not use a full column
reference (eg A:A) and make sure that both bits of the formula reference the
same number of cells each. What error are you getting, by the way?

Andy.
 
Spot-on!

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Andy B said:
Max

Looking at Bob's formula, I reckon you'll need to array-enter it. That is
instead of pressing Enter when you've typed it (or edited it) press Ctrl
Shift and Enter. The alternative is to use the same formula but use
SUMPRODUCT instead of SUM

Andy.
Click to expand...
 
Andy and Max,

SUMIF (and COUNTIF) will both take a column reference, no need to specify
the row numbers unless the range to test is to be restricted.

Also, you only need =SUMIF(M1:M68,"<0"), the third argument is not required
if the summing range is the same as the range to be tested.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Andy B said:
Max

A couple of things to check. Make sure that you do not use a full column
reference (eg A:A) and make sure that both bits of the formula reference the
same number of cells each. What error are you getting, by the way?

Andy.
Click to expand...
 
Back
Top