Solved: Sum only Positive or Negative number

[excelliot]

Hi is it possible to sum either possible number or negative number in the given range using any formulae or thru VBA

[Sir Babydum GBE]

Hi, for positive numbers try:

=SUMIF(A1:A10,">0",A1:A10)*

use <0 for negatives.

*where your range is A1:A10

[Bob Phillips]

=SUM(IF(rng>0,rng))

=SUM(IF(rng<0,rng))

which are array formulae, so commit with Ctrl-Shift-Enter

[Sir Babydum GBE]

Sorry to hijack your post excelliot, but XLD, is there an advantage to using SUM(IF over SUMIF( ? if so, what is it and when would SUMIF( be better?

[Bob Phillips]

Sorry to hijack your post excelliot, but XLD, is there an advantage to using SUM(IF over SUMIF( ? if so, what is it and when would SUMIF( be better?

No, yours is better as it is not an array formula. Dust in my eyes

[Desert Piranha]

Hi Babydum,
Could you explain the difference in these,
as they give the same result?
Which is better and why?

'=SUMIF(A1:A10,">0")
'=SUMIF(A1:A10,">0",A1:A10)
Thx

Hi, for positive numbers try:

=SUMIF(A1:A10,">0",A1:A10)*

use <0 for negatives.

*where your range is A1:A10

[Bob Phillips]

Hi Babydum,
Could you explain the difference in these,
as they give the same result?
Which is better and why?

'=SUMIF(A1:A10,">0")
'=SUMIF(A1:A10,">0",A1:A10)
Thx

SUMIF has two syntaxes, the first and most common is the SUMIF(rng1,value,rng2), which tests value against rng1, and sums the equivalent values in rng2 for matches. If rng2 is not specified, it means that the same range is used for for the test and the summing.

I would suspect that the version with no specified summing range is quicker, but I need to get to my development machine to time it. I will post some results tomorrow.

[Desert Piranha]

Hi xld,
Thanks for the enlightenment.
Thx again
Dave

SUMIF has two syntaxes, the first and most common is the SUMIF(rng1,value,rng2), which tests value against rng1, and sums the equivalent values in rng2 for matches. If rng2 is not specified, it means that the same range is used for for the test and the summing.

I would suspect that the version with no specified summing range is quicker, but I need to get to my development machine to time it. I will post some results tomorrow.

[Sir Babydum GBE]

A little extra... When I saw the first post, I thought "Ah, that's a SUMIF( thingy" But just to double check I typed a formula - like yours - into Excel, and I got the "the formula you typed contains an error" box. So I clicked the function icon to see that it was asking for two ranges.

So, given your post, the chances are that the error was a typo - not a syntax one - but I just didn't stop to look...

So now I understand better.

Having said that, I don't do too many serious posts here, so I'm glad I did one that helped someone even if it was a tad too long.