Understanding the objective '--' used in SUM PRODUCT function

[Tan]

Hi All,

I have did some read up about having '--' in writing up the SUM PRODUCT
function on some websites. But still trying to figure out the reason behind
having '--' in our SUMPRODUCT function. For example,

=SUMPRODUCT(--(PLList=B15),(ShipmKList),--(CountryList=$C$2))

PLList a named range in my database for my product code field. ShipmKList is
named range in my database for my Shipped Amt field. CountryList is named
range for my country field.

I got this function recommended by someone to extract out the total shipped
amt for a particular country and for the product codes i wish to sum. I input
my Product Code in cell B15 and input my country in cell C2 on another sheet.

Second question is how many more criteria can i set based on the SUM PRODUCT
function above and how do i go about adding more criteria.

Thanks in advance!

 

[Ragdyer]

Here is a very extensive explanation of the function:

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

 

[Tan]

Hi,

I have read thru this white paper aout SUM PRODUCT and think i have confuse
myself and the article proves too difficult for me to understand why '--' is
needed. Can you help explain the reason behind. By the way, can you help with
my second questions as well. Thanks.

 

[JE McGimpsey]

An alternative explanation:

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

 

[JE McGimpsey]

It's almost always better to post an unrelated question as a separate
thread.

From Help:
Syntax
SUMPRODUCT(array1,array2,array3, ...)
Array1, array2, array3, ... are 2 to 30 arrays
whose components you want to multiply and then add.

So you can have up to 30 arrays. Note that Help is wrong - a single
array can be entered as well.

Add arrays the same way your existing arrays are added. Just make sure
the arrays are the same size.

 

[joeu2004]

I have read thru this white paper aout SUM PRODUCT and think i have
confuse myself and the article proves too difficult for me to understand
why '--' is needed. Can you help explain the reason behind.

It does not take a "white paper" to explain this. First, in simple
arithmetic, "-" (negation) changes the sign of the operand to the
right. So, if A1 contains -1, -A1 is 1, and --A1 is -1. Second, the
purpose of "--" (double negation) in some SUMPRODUCT() usage is to
convert a non-numeric value to a numeric one. For example, "(A1=B1)"
returns a truth value -- true or false. "--(A1=B1)" causes the truth
value to be treated as a number, namely 1 or 0 respectively.

You wrote earlier:

=SUMPRODUCT(--(PLList=B15),(ShipmKList),--(CountryList=$C$2))
[....]
Second question is how many more criteria can i set based on the SUM
PRODUCT function above and how do i go about adding more criteria

Use Help -> Excel Help to see the explanation of SUMPRODUCT. The
function is limited to 30 arguments. However, in some cases, you can
increase the number of conditions by using "clever" arithmetic. For
example, I believe that the following are equivalent (making no value
judgment about them):

=sumproduct(--(PLList=B15), --(CountryList=$C$2))

=sumproduct ((PLList=B15)*(CountryList=$C$2))

Note that no "--" is needed in the second case because "*" causes the
operands to be treated as numeric, resulting in a numeric result, not
a truth value.

 

[T. Valko]

Note that no "--" is needed in the second case because "*" causes the

operands to be treated as numeric, resulting in a numeric result, not
a truth value

The double unary is never "needed". Someone discovered that it does the same
thing as multiplying the arrays and is slightly more efficient. What does
Help say about the double unary?

It isn't even mentioned!

Biff

I have read thru this white paper aout SUM PRODUCT and think i have
confuse myself and the article proves too difficult for me to understand
why '--' is needed. Can you help explain the reason behind.

It does not take a "white paper" to explain this. First, in simple
arithmetic, "-" (negation) changes the sign of the operand to the
right. So, if A1 contains -1, -A1 is 1, and --A1 is -1. Second, the
purpose of "--" (double negation) in some SUMPRODUCT() usage is to
convert a non-numeric value to a numeric one. For example, "(A1=B1)"
returns a truth value -- true or false. "--(A1=B1)" causes the truth
value to be treated as a number, namely 1 or 0 respectively.

You wrote earlier:

=SUMPRODUCT(--(PLList=B15),(ShipmKList),--(CountryList=$C$2))
[....]
Second question is how many more criteria can i set based on the SUM
PRODUCT function above and how do i go about adding more criteria

Use Help -> Excel Help to see the explanation of SUMPRODUCT. The
function is limited to 30 arguments. However, in some cases, you can
increase the number of conditions by using "clever" arithmetic. For
example, I believe that the following are equivalent (making no value
judgment about them):

=sumproduct(--(PLList=B15), --(CountryList=$C$2))

=sumproduct ((PLList=B15)*(CountryList=$C$2))

Note that no "--" is needed in the second case because "*" causes the
operands to be treated as numeric, resulting in a numeric result, not
a truth value.

 

[Harlan Grove]

The double unary is never "needed". . . .

Yup. Any idempotent arithmetic operation would do: --TF, TF+0, TF*1,
TF^1. Some arithmetic operation is needed to convert TRUE to numeric 1
and FALSE to numeric 0, and that's ALL the operation should do.

Same could be accomplished with IF(TF,1,0) or N(TF), but those both
eat a nested function call level, and the former only works in
formulas entered as array formulas, and the latter only works with
derived arrays.

What does Help say about the double unary?

It isn't even mentioned!

It shouldn't be. What should be mentioned more prominently is which
functions automatically convert their numeric string or boolean
arguments to numbers automatically and which don't. For example, my
favorite, least orthogonal function in Excel, NPV:

=NPV("100%",{1;2;3}) returns 1.375 rather than 6

and this isn't due to scalar vs array/range semantics,

=SUM("1") returns 1
=SUM({"1";"2";"3"}) returns 0

but

=NPV({"0%","100%"},{1;2;3}) returns {6,1.375} rather than {6,6}

and FTHOI

=NPV({TRUE,FALSE},{1;2;3}) returns {1.375,6} rather than {6,6}

So MSFT *COULD* have chosen to have SUMPRODUCT convert booleans and
numeric strings in entries in its array arguments into numbers, but it
didn't. Therefore, explicit type conversion is necessary, and
idempotent arithmetic operations just happen to be the most efficient
means to do that, and -- just happens to have certain benefits
compared to the alternatives. Pity unary + can't affect the same
result.

 

[Ragdyer]

I'm on your side with this Biff.
The use (or *over use*) of that unary has been a constant thorn in my side
when it comes to trying to calculate large datalists of numbers, where it
(unary) allows tainted values to be bypassed instead of flagging them (with
#VALUE! errors) so that they might be corrected.

However, I believe your argument, of it not "even" appearing in the Help
files, needs to be re-thought.<g>

These XL Help files are such a big joke, that *not* being mentioned might be
considered an endorsement.

Look at Datedif() and the *very extensive* description of Sumproduct()
itself.<vbg>
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

Note that no "--" is needed in the second case because "*" causes the
operands to be treated as numeric, resulting in a numeric result, not
a truth value

The double unary is never "needed". Someone discovered that it does the same
thing as multiplying the arrays and is slightly more efficient. What does
Help say about the double unary?

It isn't even mentioned!

Biff

I have read thru this white paper aout SUM PRODUCT and think i have
confuse myself and the article proves too difficult for me to understand
why '--' is needed. Can you help explain the reason behind.

It does not take a "white paper" to explain this. First, in simple
arithmetic, "-" (negation) changes the sign of the operand to the
right. So, if A1 contains -1, -A1 is 1, and --A1 is -1. Second, the
purpose of "--" (double negation) in some SUMPRODUCT() usage is to
convert a non-numeric value to a numeric one. For example, "(A1=B1)"
returns a truth value -- true or false. "--(A1=B1)" causes the truth
value to be treated as a number, namely 1 or 0 respectively.

You wrote earlier:

=SUMPRODUCT(--(PLList=B15),(ShipmKList),--(CountryList=$C$2))
[....]
Second question is how many more criteria can i set based on the SUM
PRODUCT function above and how do i go about adding more criteria

Use Help -> Excel Help to see the explanation of SUMPRODUCT. The
function is limited to 30 arguments. However, in some cases, you can
increase the number of conditions by using "clever" arithmetic. For
example, I believe that the following are equivalent (making no value
judgment about them):

=sumproduct(--(PLList=B15), --(CountryList=$C$2))

=sumproduct ((PLList=B15)*(CountryList=$C$2))

Note that no "--" is needed in the second case because "*" causes the
operands to be treated as numeric, resulting in a numeric result, not
a truth value.

 

[JE McGimpsey]

The double unary does *not* do the same thing as multiplying the arrays,
and that is what likely leads to the increased efficiency.

When one uses the form

=SUMPRODUCT(arr1 * arr2)

the arrays are converted to numeric arrays, then multiplied, then the
result (a single array) is passed to the SUMPRODUCT function to be
summed.

When one uses the form

=SUMPRODUCT(--arr1,--arr2)

the arrays are converted to numeric arrays, then both arrays are passed
to SUMPRODUCT to be multiplied and summed.

The end result may be the same, but it would not be surprising that a
function optimized to multiply arrays would be somewhat more efficient
than the multiplication operator passed two arrays.

Not sure what you mean about Help - neither the unary minus nor the
multiplication operator are mentioned in Help's SUMPRODUCT topic, but
both are mentioned under Calculation operators.

 

[Harlan Grove]

The double unary does *not* do the same thing as multiplying the
arrays, and that is what likely leads to the increased efficiency.

When one uses the form

=SUMPRODUCT(arr1 * arr2)

....

With boolean arrays, it's actually scalar * array.

When one uses the form

=SUMPRODUCT(--arr1,--arr2)

the arrays are converted to numeric arrays, then both arrays are
passed to SUMPRODUCT to be multiplied and summed.

....

The arrays are NOT passed directly to SUMPRODUCT as arrays entirely of
numbers. The -- applies two arithmetic sign changes to the arrays.
However, arithmetic sign change is a faster FPU operation than +0, *1
or ^1. It should be since it involves nothing more than flipping the
sign bit. Even if +0, *1 and ^1 were treated as special cases in the
FPU's firmware, it'd take cycles to check the operands.

 

[T. Valko]

The double unary is never "needed". Someone discovered that

The double unary does *not* do the same thing as multiplying the arrays

Let me rephrase that, then:

The double unary is never "needed". Someone discovered that it has the same
net effect as multiplying the arrays......

Biff