PDA

View Full Version : Solved: Sum only Positive or Negative number



excelliot
12-15-2005, 12:07 AM
Hi is it possible to sum either possible number or negative number in the given range using any formulae or thru VBA:dunno

Sir Babydum GBE
12-15-2005, 02:15 AM
Hi, for positive numbers try:

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

use <0 for negatives.

*where your range is A1:A10

Bob Phillips
12-15-2005, 02:16 AM
=SUM(IF(rng>0,rng))

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

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

Sir Babydum GBE
12-15-2005, 02:20 AM
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
12-15-2005, 03:15 AM
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 http://vbaexpress.com/forum/images/smilies/doh.gif

Desert Piranha
12-15-2005, 02:42 PM
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
12-15-2005, 02:47 PM
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
12-15-2005, 05:15 PM
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
12-15-2005, 05:21 PM
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.