SUM positive and negative nos 2

[mart10]

I am using Excel 2003
I have a list of numbers as below example. The list may vary in length and sign of numbers may change on any row

-2
-4
6
-15
9

I want to sum the neagive and positive number in the column seperately. So i would expect to see answers of -21 and 15

 

[mart10]

Thanks, its all working and this thread seems to have provoked some discusion

 

[N1GHTEYES]

Glen / Skip, thanks for patiently answering my questions.

I'm still slightly puzzled as to why

SUMPRODUCT(range1,range2)

and

SUMPRODUCT(range1*range2)

seem to give identical results. You would think that, in the latter case, it should not work as SUMPRODUCT expects two or more arguments and "range1*range2" is actually just one.

I'd love to understand exactly how Excel treats this, and why it reacts the way it does. However, I'm happy, if necessary, to chalk it up as another little Excel quirk.

Tony

 

[combo]

SUMPRODUCT accepts one argument too, SUMPRODUCT(range1) returns the same result as SUM(range1). In oposite to SUM, as Gavin pointed, one can have SUMPRODUCT(ABS(range1)), but SUM(ABS(range1)) neds to be array entered.
So the SUMPRODUCT can be a (better) substitute for some array formulas.

combo

 

[N1GHTEYES]

Thanks combo.

I've just been having a play with SUM & SUMPRODUCT entered with "," or "*" and entered plain or as array formulae and I'd just come to pretty much the same conclusion.

So under what circumstances do you think SUMPRODUCT is better than SUM entered as an array formula?

Tony

 

[combo]

Most of all - speed, esp. in case of big tables used. Array formulas are recalculated each time the workbook is changed (same as UDFs).


combo

 

[N1GHTEYES]

Even if the source ranges don't? I never realised that.

Useful to know.

Tony

 

[combo]

In fact, it's the case when one entry changes, the whole array formula is recalculated (so it's for more than one cell output).
I found it interesting:

http://www.decisionmodels.com/optspeedj.htm

combo

 

[N1GHTEYES]

Thanks again combo. That site has all the explanations I could have wished for. Have a star.

Actually, I had come across that site before, but I'd forgotten about it. That seems to happen more and more these days...

Tony

 

[combo]

Thanks, I keep this site in my library, I had an opportunity to recall it (total recall?).

combo

 

[N1GHTEYES]

They stole his mind ...and now he wants it back!

That line always killed me.

I've finally remembered to put it mine now too.

Tony

 

[kjv1611]

Thanks for posting that link, combo. I don't think I've come across it before - unless I just totally forgot.

 

[MCuthill]

*jumps into thread with reckless abandon*

Another handy site I found (and have bookmarked at work and home) for explaining some of Excel's more robust (and simple) functions is [URL unfurl="true"]http://www.cpearson.com/excel/topic.aspx[/url]

I noticed it doesn't have a specific topic on SUMPRODUCT, but has many pages on the functionality of array's.

Cheers!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[kjv1611]

Yes, Chip Pearson has loads of useful information for sure.