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

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

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

HTH
Peter A

"Michelle" wrote:

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" wrote in message
...
Bob Philips has a great page on SUMPRODUCT and double unaries '--'

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

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

Bernd P wrote:
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

=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:C 100)

--
Biff
Microsoft Excel MVP


"Glenn" wrote in message
...
Bernd P wrote:
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

--
__________________________________
HTH

Bob

"Glenn" wrote in message
...
Bernd P wrote:
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.

Thanks Guys, it genuinely is all clear now.

Brilliant

M

"Billy Liddel" wrote in message
...
Bob Philips has a great page on SUMPRODUCT and double unaries '--'

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

HTH
Peter A

"Michelle" wrote:

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

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

--
David Biddulph


"Michelle" wrote in message
...
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

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

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

Hope this helps.

Pete

On Oct 1, 2:32*pm, "Michelle" wrote:
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