Sum Product Quandry

[JimS]

I've got this formula in a cell:

=SUMPRODUCT(($D$14:$D$2002="d")*($E$14:$E$2002="r")*($N$14:$N$2002))

I tried this and it works fine for any column (N) that is not the
result of a formula. The data in column N is the result of a long
formula, and when I try and use this sum product equation to total the
values in column N I get the VALUE error.

How can I get around this?

 

[Pete_UK]

Post an example of the formula in column N - perhaps it returns a
number in quotes rather than a true number.

Hope this helps.

Pete

 

[JimS]

OK, here it is:

=IF(J15="","",IF(L15="",(G15+I15)*-1,IF(K15=F15,G15/2*L15-(G15+I15),IF(K15=H15,I15/2*L15-(G15+I15)))))

 

[Bob Phillips]

=SUMPRODUCT(--($D$14:$D$2002="d"),--($E$14:$E$2002="r"),$N$14:$N$2002)

 

[JimS]

Thanks. I see what you did there, but I don't understand it. Either
it's a minus sign or a double dash. What exactly did that do, and why
did you need to do it that way?

Thanks again, much appreciated.

 

[John]

Hi graciously took this information from PCLIVE, hope he does't mind.

A double minus is also known as a double urnary.

In this formula: =SUMPRODUCT(--(A1:A10 & D110="1A"))

this section: (A1:A10 & D110="1A")
returns a series of boolean (fancy word for: TRUE/FALSE) values
....which are not numeric to Excel.

When an arithmetic operator (+,-,*,/) is applied to a boolean 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

Now, you could easily 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 the SUMPRODUCT calculates the total.

- explanation contribution thanks to Ron Coderre from a previous post

HTH
John

 

[Bob Phillips]

No, using the * gives the error, the -- was used here to bypass that error.
And it's purpose is to coerce TRUE/FALSE to 11/0 just like *, but * fails
because it tries to multiply by "", whereas using -- avoids the *, and the
SUM part of SUMPRODUCT adds the numbers ignoring the text values

 

[Bob Phillips]

Actually, although the double unary is my preferred way, this also works

=SUMPRODUCT(($D$14:$D$2002="d")*($E$14:$E$2002="r"),$N$14:$N$2002)

 

[Bernard Liengme]

Why are we using the phase 'double unary' for -- ?
A unary operator has one operand but does not necessarily imply negation.
So ++10 is also a double unary as is Sqrt(Sqrt(A1))
Why not call - - 'double negation'
best wishes from a pedantic

 

[JimS]

Thanks to all for the explanations.

Hi graciously took this information from PCLIVE, hope he does't mind.

A double minus is also known as a double urnary.

In this formula: =SUMPRODUCT(--(A1:A10 & D110="1A"))

this section: (A1:A10 & D110="1A")
returns a series of boolean (fancy word for: TRUE/FALSE) values
...which are not numeric to Excel.

When an arithmetic operator (+,-,*,/) is applied to a boolean 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

Now, you could easily 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 the SUMPRODUCT calculates the total.

- explanation contribution thanks to Ron Coderre from a previous post

HTH
John