Sumproduct Fucntion - the use of dash ( -- )

[Negda]

In some example I saw the use of double dashes at the beginning:
=sumproduct( -- (....))

What does it mean? when I should use it and to avoid?

 

[Pete_UK]

The terms in brackets in a sumproduct formula will be logical terms
and thus will return values of FALSE or TRUE. Using a single minus
will change these to 0 and -1, and a double minus converts them to 0
or 1, so they can be used in arithmetic. You can use them like this in
a SP formula:

=SUMPRODUCT(--(condition1),--(condition2),--(condition3))

but an alternative to this is:

=SUMPRODUCT((condition1)*(condition2)*(condition3))

Hope this helps.

Pete

 

[Ron Coderre]

In this formula: =SUMPRODUCT(--(A1:A10="Open"))

this section: (A1:A10="Open")
returns a series of TRUE/FALSE values
....which are not numeric to Excel.

When an arithmetic operator (+,-,*,/) is applied to
a TRUE/FALSE value, Excel converts TRUE to 1 and FALSE to 0.

The standard convention is to use
the Double-Minus (--) to convert the values.

It works this way:
TRUE=TRUE
-TRUE = -1
--TRUE = 1

FALSE = FALSE
-FALSE = 0
--FALSE = 0

You could also use 1*TRUE, but the Dbl-Minus indicates to
knowledgable users that you are forcing a conversion and not
trying to calculate something.

So, in the formula, the TRUE/FALSE values
are converted to 1's and 0's by the "--" and
SUMPRODUCT calculates the result.

Does that help?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

 

[Negda]

The terms in brackets in a sumproduct formula will be logical terms
and thus will return values of FALSE or TRUE. Using a single minus
will change these to 0 and -1, and a double minus converts them to 0
or 1, so they can be used in arithmetic. You can use them like this in
a SP formula:

=SUMPRODUCT(--(condition1),--(condition2),--(condition3))

but an alternative to this is:

=SUMPRODUCT((condition1)*(condition2)*(condition3))

Hope this helps.

Pete




- Show quoted text -

thank you very much - helps a lot
Negda

 

[Negda]

In this formula: =SUMPRODUCT(--(A1:A10="Open"))

this section: (A1:A10="Open")
returns a series of TRUE/FALSE values
...which are not numeric to Excel.

When an arithmetic operator (+,-,*,/) is applied to
a TRUE/FALSE value, Excel converts TRUE to 1 and FALSE to 0.

The standard convention is to use
the Double-Minus (--) to convert the values.

It works this way:
TRUE=TRUE
-TRUE = -1
--TRUE = 1

FALSE = FALSE
-FALSE = 0
--FALSE = 0

You could also use 1*TRUE, but the Dbl-Minus indicates to
knowledgable users that you are forcing a conversion and not
trying to calculate something.

So, in the formula, the TRUE/FALSE values
are converted to 1's and 0's by the "--" and
SUMPRODUCT calculates the result.

Does that help?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)







- Show quoted text -

Ron,
Yes, it helps - thanks
Negda

 

[ryguy7272]

Look here for a robust explanation of the Unary Operator:
http://bhinneka.blogspot.com/2008_02_01_archive.html

Regards,
Ryan---

 

[Pete_UK]

You're welcome - thanks for taking the trouble to feed back.

Pete

 

[plb2862]

In this formula: =SUMPRODUCT(--(A1:A10="Open"))

this section: (A1:A10="Open")
returns a series of TRUE/FALSE values
...which are not numeric to Excel.

When an arithmetic operator (+,-,*,/) is applied to
a TRUE/FALSE value, Excel converts TRUE to 1 and FALSE to 0.

The standard convention is to use
the Double-Minus (--) to convert the values.

It works this way:
TRUE=TRUE
-TRUE = -1
--TRUE = 1

FALSE = FALSE
-FALSE = 0
--FALSE = 0

You could also use 1*TRUE, but the Dbl-Minus indicates to
knowledgable users that you are forcing a conversion and not
trying to calculate something.

So, in the formula, the TRUE/FALSE values
are converted to 1's and 0's by the "--" and
SUMPRODUCT calculates the result.

Does that help?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

I've been trying to sum multiple columns where a condition does not
exeist in one of the columns. Example: columns x, y & z where z doesn't
contain "this condition".

I can understand that these work:
=SUMPRODUCT((condition1)*(condition2)*(condition3))
and even =SUMPRODUCT(--(condition1),--(condition2),--(condition3))
but I can't understand how to get to my desired results. Would I write
the formula like this?
=SUMPRODUCT((condition1)*(condition2)-(condition3))
=SUMPRODUCT(--(condition1),--(condition2),-(condition3))

TIA

 

[Ron Coderre]

Can you give some more details about the
kind of test you want to perform on Col_Z?

--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

 

[plb2862]

Can you give some more details about the
kind of test you want to perform on Col_Z?

--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

Thanks for getting back to me. Yes, (condition 3) is not in column z.
TIA

 

[Ron Coderre]

I'm sorry; I wasn't clear.

The SUMPRODUCT structure you'll need will
be dependent on the kind of data you are
testing and the kind of test you want to do.

Are we testing if each corresponding cell in Col_Z:
.. is a number?
.. is a specific number?
.. is text?
.. is specific text?
.. matches an item in a list?
Example:
Multiply each cell in X1:X100
by each corresponding cell in Y1:Y100
if the corresponding cell in Z1:Z100
does not equal "Canceled".

or..are we testing if any of the above
conditions exist anywhere in Col_Z?
Example:
Multiply each cell in X1:X100
by each corresponding cell in Y1:Y100
BUT only if "Canceled" does NOT exist
anywhere in Z1:Z100

In the meantime, perhaps this kind of structure:
=SUMPRODUCT(--(Z1:Z100<>"CANCELED"),--(X1:X100),--(Y1:Y100))


--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

 

[plb2862]

I'm sorry; I wasn't clear.

The SUMPRODUCT structure you'll need will
be dependent on the kind of data you are
testing and the kind of test you want to do.

Are we testing if each corresponding cell in Col_Z:
. is a number?
. is a specific number?
. is text?
. is specific text?
. matches an item in a list?
Example:
Multiply each cell in X1:X100
by each corresponding cell in Y1:Y100
if the corresponding cell in Z1:Z100
does not equal "Canceled".

or..are we testing if any of the above
conditions exist anywhere in Col_Z?
Example:
Multiply each cell in X1:X100
by each corresponding cell in Y1:Y100
BUT only if "Canceled" does NOT exist
anywhere in Z1:Z100

In the meantime, perhaps this kind of structure:
=SUMPRODUCT(--(Z1:Z100<>"CANCELED"),--(X1:X100),--(Y1:Y100))


--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

Thanks again, (condition 3) would be specific text to exclude the record.