SUMPRODUCT and "--"

[Michelle]

I don't understand why sometimes I have to use "--" before an argument in
the SUMPRODUCT fuction, and sometimes a "*".

Can anyone point me to a resource that will explain why and when I have to
use these instead of just commas, so that I don't have to post any more
questions about it!

Thanks

M

 

[Billy Liddel]

Bob Philips has a great page on SUMPRODUCT and double unaries '--'

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

HTH
Peter A

 

[David Biddulph]

Double unary minus:
a.. http://www.mcgimpsey.com/excel/formulae/doubleneg.html
b.. http://xldynamic.com/source/xld.SUMPRODUCT.html

 

[Pete_UK]

Here's another explanation, from John McGimpsey's site:

http://www.mcgimpsey.com/excel/formulae/doubleneg.html

Hope this helps.

Pete

 

[Bob Phillips]

Bob Philips has a great page on SUMPRODUCT and double unaries '--'

He also has 2 ls in his surname, Welsh origins not Dutch <bg>

 

[Bernd P]

Bob Philips has a great page on SUMPRODUCT and double unaries '--'

He also has 2 ls in his surname, Welsh origins not Dutch <bg>

Hello,

But he still does not show a SIGN() function around his example with
two OR criteria on that page <vbg>

Compare
http://xldynamic.com/source/xld.SUMPRODUCT.html
[look for "=SUMPRODUCT((A1:A10="Ford")+(A1:A10="Renault"))"]
to
http://sulprobil.com/html/sumproduct.html
[look for "OR Condition"]
and judge yourself.

Regards,
Bernd

 

[Glenn]

He also has 2 ls in his surname, Welsh origins not Dutch <bg>

Hello,

But he still does not show a SIGN() function around his example with
two OR criteria on that page <vbg>

Compare
http://xldynamic.com/source/xld.SUMPRODUCT.html
[look for "=SUMPRODUCT((A1:A10="Ford")+(A1:A10="Renault"))"]

Both ranges are the same and since the same cell can't equal both "Ford" and
"Renault", this will produce the expected result.

to
http://sulprobil.com/html/sumproduct.html
[look for "OR Condition"]

=SUMPRODUCT(SIGN(--(A1:A100<0)+(B1:B100=”YES”)),C1:C100)

Two distinct ranges. Since A1=-3 and B1="Yes" is possible, it is a different
problem requiring a more complex solution.

and judge yourself.

Both solutions appear to be valid, but one is more robust.

 

[Michelle]

Thanks Guys, it genuinely is all clear now.

Brilliant

M

 

[T. Valko]

=SUMPRODUCT(SIGN(--(A1:A100<0)+(B1:B100=”YES”)),C1:C100)

Also, in this application the double unary is redundant. Adding the 2 arrays
will coerce the Boolean to numeric:

=SUMPRODUCT(SIGN((A1:A100<0)+(B1:B100=”YES”)),C1:C100)

--
Biff
Microsoft Excel MVP

Bob Philips has a great page on SUMPRODUCT and double unaries '--'
He also has 2 ls in his surname, Welsh origins not Dutch <bg>

Hello,

But he still does not show a SIGN() function around his example with
two OR criteria on that page <vbg>

Compare
http://xldynamic.com/source/xld.SUMPRODUCT.html
[look for "=SUMPRODUCT((A1:A10="Ford")+(A1:A10="Renault"))"]

Both ranges are the same and since the same cell can't equal both "Ford"
and "Renault", this will produce the expected result.

to
http://sulprobil.com/html/sumproduct.html
[look for "OR Condition"]

=SUMPRODUCT(SIGN(--(A1:A100<0)+(B1:B100=”YES”)),C1:C100)

Two distinct ranges. Since A1=-3 and B1="Yes" is possible, it is a
different problem requiring a more complex solution.

and judge yourself.

Both solutions appear to be valid, but one is more robust.

Regards,
Bernd

 

[Bob Phillips]

Also, in this application the double unary is redundant. Adding the 2
arrays will coerce the Boolean to numeric:

=SUMPRODUCT(SIGN((A1:A100<0)+(B1:B100="YES")),C1:C100)

As mine does (licks finger, paints the sky!)

 

[Bob Phillips]

--
__________________________________
HTH

Bob

Bob Philips has a great page on SUMPRODUCT and double unaries '--'
He also has 2 ls in his surname, Welsh origins not Dutch <bg>

Hello,

But he still does not show a SIGN() function around his example with
two OR criteria on that page <vbg>

Compare
http://xldynamic.com/source/xld.SUMPRODUCT.html
[look for "=SUMPRODUCT((A1:A10="Ford")+(A1:A10="Renault"))"]

Both ranges are the same and since the same cell can't equal both "Ford"
and "Renault", this will produce the expected result.


Exactly!

to
http://sulprobil.com/html/sumproduct.html
[look for "OR Condition"]

=SUMPRODUCT(SIGN(--(A1:A100<0)+(B1:B100=”YES”)),C1:C100)

Two distinct ranges. Since A1=-3 and B1="Yes" is possible, it is a
different problem requiring a more complex solution.

and judge yourself.

Both solutions appear to be valid, but one is more robust.

No more robust, my example is specifically showing an OR on the same range,
in such circumstances it will not fail so it is robust. You have to look at
it in the context of how/where it is presented.

 

[JoeU2004]

=SUMPRODUCT(SIGN(--(A1:A100<0)+(B1:B100="YES")),C1:C100)

Also, in this application the double unary is redundant.
[....]
=SUMPRODUCT(SIGN((A1:A100<0)+(B1:B100="YES")),C1:C100)

Or:

=SUMPRODUCT((A1:A100<0)+(B1:B100="YES")>0, C1:C100)

Since I suspect that Excel treats SIGN as an operator, not a bona fide
function with its concomitant overhead, it is unclear whether my suggestion
is more efficient. But it requires 4 fewer keystrokes to type .


----- original message -----

=SUMPRODUCT(SIGN(--(A1:A100<0)+(B1:B100="YES")),C1:C100)

Also, in this application the double unary is redundant. Adding the 2
arrays will coerce the Boolean to numeric:

=SUMPRODUCT(SIGN((A1:A100<0)+(B1:B100="YES")),C1:C100)

--
Biff
Microsoft Excel MVP

Bob Philips has a great page on SUMPRODUCT and double unaries '--'
He also has 2 ls in his surname, Welsh origins not Dutch <bg>
Hello,

But he still does not show a SIGN() function around his example with
two OR criteria on that page <vbg>

Compare
http://xldynamic.com/source/xld.SUMPRODUCT.html
[look for "=SUMPRODUCT((A1:A10="Ford")+(A1:A10="Renault"))"]

Both ranges are the same and since the same cell can't equal both "Ford"
and "Renault", this will produce the expected result.

to
http://sulprobil.com/html/sumproduct.html
[look for "OR Condition"]

=SUMPRODUCT(SIGN(--(A1:A100<0)+(B1:B100="YES")),C1:C100)

Two distinct ranges. Since A1=-3 and B1="Yes" is possible, it is a
different problem requiring a more complex solution.

and judge yourself.

Both solutions appear to be valid, but one is more robust.

Regards,
Bernd

 

[Glenn]

Bob Phillips wrote:

"No more robust, my example is specifically showing an OR on the same range,
in such circumstances it will not fail so it is robust. You have to look at
it in the context of how/where it is presented."


I guess what I meant was that Bernd's solution would work for either problem and
yours wouldn't.

 

[Bernd P]

Also, in this application the double unary is redundant. Adding the 2 arrays
will coerce the Boolean to numeric:

Hello Biff,

Thank you - adjusted.

Regards,
Bernd

 

[Bernd P]

Since I suspect that Excel treats SIGN as an operator, not a bona fide
function with its concomitant overhead, it is unclear whether my suggestion
is more efficient.  But it requires 4 fewer keystrokes to type .

Hello,

That is possible and also far better than just to add the OR criteria
checks.
But I prefer the SIGN() function - it is clearly showing that we deal
with OR criteria here. This is similar to the double unary minus
operator which clearly indicates a criterion check.

But let us keep in mind that we talk about Excel's biggest time
waster: SUMPRODUCT

Regards,
Bernd

 

[JoeU2004]

But I prefer the SIGN() function

To each his own.

But philosophically, I think ">0" is the better choice. It clearly
indicates that we expect only values greater than zero to be valid.

When I saw SIGN, my first reaction was: "Why is the guy using SIGN? The
particular argument can never evaluate to -1. So what does he expect?"

Then I thought: "Okay, it works. But why use a function when an operator
will do just fine?" Then I remembered that IF() is treated like an
operator, not a function, so I thought perhaps that is true for all internal
functions. So there probably is no performance issue. Nonetheless, the use
of SIGN __looks__ inefficient there. And of course, it counts toward the
Excel 2003 nesting limit of 7.

But hey, y'can lead a horse to water, but y'can't make him drink.


----- original message -----

Since I suspect that Excel treats SIGN as an operator, not a bona fide
function with its concomitant overhead, it is unclear whether my
suggestion
is more efficient. But it requires 4 fewer keystrokes to type .

Hello,

That is possible and also far better than just to add the OR criteria
checks.
But I prefer the SIGN() function - it is clearly showing that we deal
with OR criteria here. This is similar to the double unary minus
operator which clearly indicates a criterion check.

But let us keep in mind that we talk about Excel's biggest time
waster: SUMPRODUCT

Regards,
Bernd

 

[T. Valko]

But let us keep in mind that we talk about Excel's

biggest time waster: SUMPRODUCT

From your website:

My thesis is that SUMPRODUCT has become the biggest time-waster for Excel ©
users who managed to understand its functionality.

If that's what you think then why do you also suggest (on your website)
using SUMPRODUCT instead of SUMIF/COUNTIF?

Wouldn't that be a waste of time? <g>

--
Biff
Microsoft Excel MVP

Since I suspect that Excel treats SIGN as an operator, not a bona fide
function with its concomitant overhead, it is unclear whether my
suggestion
is more efficient. But it requires 4 fewer keystrokes to type .

Hello,

That is possible and also far better than just to add the OR criteria
checks.
But I prefer the SIGN() function - it is clearly showing that we deal
with OR criteria here. This is similar to the double unary minus
operator which clearly indicates a criterion check.

But let us keep in mind that we talk about Excel's biggest time
waster: SUMPRODUCT

Regards,
Bernd

 

[Bernd P]

To each his own.

But philosophically, I think ">0" is the better choice.  It clearly
indicates that we expect only values greater than zero to be valid.

When I saw SIGN, my first reaction was:  "Why is the guy using SIGN?  The
particular argument can never evaluate to -1.  So what does he expect?"

Then I thought:  "Okay, it works.  But why use a function when an operator
will do just fine?"  Then I remembered that IF() is treated like an
operator, not a function, so I thought perhaps that is true for all internal
functions.  So there probably is no performance issue.  Nonetheless, the use
of SIGN __looks__ inefficient there.  And of course, it counts toward the
Excel 2003 nesting limit of 7.

But hey, y'can lead a horse to water, but y'can't make him drink.

Hello,

Your ()>0 is only doing half of the job because its not resulting in
numbers, just boolean values. In order to use it generally within
nested or combined conditions (example: an AND of two OR conditions)
you will need to wrap it into paranthesis and to double unary it (or
similar). So: welcome back to the same number of keystrokes and
nesting levels.

Regards,
Bernd

 

[Bernd P]

From your website:

My thesis is that SUMPRODUCT has become the biggest time-waster for Excel©
users who managed to understand its functionality.

If that's what you think then why do you also suggest (on your website)
using SUMPRODUCT instead of SUMIF/COUNTIF?

Wouldn't that be a waste of time? <g>

Hello Biff,

No.

1. For some problems you should use SUMPRODUCT, for some you should
use my UDF's (or other special-purpose UDF's) and to some others you
should apply pivot tables.

2. SUMPRODUCT is offered here (and then used or applied) far too
often.

These two thesises are not forming a contradiction.

Regards,
Bernd

 

[T. Valko]

2. SUMPRODUCT is offered here (and then used or applied) far too often.

Maybe the reason it's applied so often is because it's a very powerful and
useful counting/summing/"lookup" function.

--
Biff
Microsoft Excel MVP

From your website:

My thesis is that SUMPRODUCT has become the biggest time-waster for Excel
©
users who managed to understand its functionality.

If that's what you think then why do you also suggest (on your website)
using SUMPRODUCT instead of SUMIF/COUNTIF?

Wouldn't that be a waste of time? <g>

Hello Biff,

No.

1. For some problems you should use SUMPRODUCT, for some you should
use my UDF's (or other special-purpose UDF's) and to some others you
should apply pivot tables.

2. SUMPRODUCT is offered here (and then used or applied) far too
often.

These two thesises are not forming a contradiction.

Regards,
Bernd