SUMPRODUCT with )--

[Paul Dennis]

Hi,

I know what -- does however I have seen it a couple of ways, syntax
sumproduct(--(criteria),--(criteria)) and also
sumproduct(--(criteria)--(criteria))

what is the difference in using the , or not?

thx

 

[Peo Sjoblom]

If you use the unary minuses you would always need a comma as a delimiter so
your second example is wrong. You can also use

sumproduct((.....)*(....))

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


--


Regards,


Peo Sjoblom

 

[Paul Dennis]

As an example I have
=SUMPRODUCT(--(ProblemClosedDate>=$B21)--(ActionClosedDate>=$C21),--(ProblemClosedDate<>""),--(ProblemOpenDate<$F20),--(LOB=$E21),--(DueDateVersion>$D21))
and it returns 21. I have checked my numbers and the result matches what I
want. If I however add a comma after the first criteria then it returns 0
which is incorrect.

My initial though was the , changed the criteria from an additional filter
to an and i.e. both the criteria had to match.

 

[Bob Phillips]

That version cannot be correct. This

--(ProblemClosedDate>=$B21)--(ActionClosedDate>=$C21)

will return an array of 0, 1 and instead of two arrays of 0, 1. As the
arrays then get multiplied by other arrays, some items can/will get double
counted because of the 2s.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Rick Rothstein \(MVP - VB\)]

Two unary minus signs is the same as a plus sign, but affixing a plus sign
to an expression does not force a calculation to take place. When no
calculations are being done to a logical expressions (or a number that is
being returned as text), the double unary forces a calculation (minus one
times minus one times the TRUE or FALSE returned by the logical expression)
to take place... that is why it is used. The first argument in the
SUMPRODUCT function in the formula you posted does, in fact, have a
calculation taking place, so the double unary minus signs are unnecessary.
This should return the same thing your formula does...

=SUMPRODUCT((ProblemClosedDate>=$B21)+(ActionClosedDate>=$C21),--(ProblemClosedDate<>""),--(ProblemOpenDate<$F20),--(LOB=$E21),--(DueDateVersion>$D21))

although I have to admit that looks like an odd construction to me. Are you
sure there is not missing comma between the first two arguments of the
SUMPRODUCT function in the formula as you originally posted it?

Rick

 

[Peo Sjoblom]

It's impossible to say without knowing what you want this formula to do


(--(ProblemClosedDate>=$B21)--(ActionClosedDate>=$C21)


is the same as


((ProblemClosedDate>=$B21)+(ActionClosedDate>=$C21)

so if the first range returns 3 TRUE and the second 2 TRUE it will
total up to 5 so it is similar to an OR function not an AND meaning that
if

ProblemClosedDate>=$B21 returns FALSE for one particular row/column
and ActionClosedDate returns TRUE for the same particular row/column

it will still count as 1 while


(--(ProblemClosedDate>=$B21),--(ActionClosedDate>=$C21)


will return zero for the same, to get 1 for the latter both criteria need to
be TRUE so it would need 2,


SUMPRODUCT(--(ProblemClosedDate>=$B21)OR(ActionClosedDate>=$C21)AND(ProblemClosedDate<>"")AND(ProblemOpenDate<$F21)AND(LOB=$E21)AND(DueDateVersion>$D21))


meaning that if the first 2 criteria are TRUE they will count as 2 as long
as the other criteria are TRUE, if one is TRUE and the other is FALSE they
will count as 1, if you would use the comma between the first 2 both would
need to be TRUE to count as 1



--


Regards,


Peo Sjoblom