Sum Product Quandry

  • Thread starter Thread starter JimS
  • Start date Start date
J

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?
 
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
 
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)))))
 
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.
 
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 & D1:D10="1A"))

this section: (A1:A10 & D1:D10="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
 
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
 
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)
 
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
 
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 & D1:D10="1A"))

this section: (A1:A10 & D1:D10="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
Click to expand...
 
Back
Top