SUMPRODUCT

  • Thread starter Thread starter PAL
  • Start date Start date
P

PAL

Any idea how the output would be a negative number? I think the value is
right, but the negative doesn't make sense. Thanks.

=SUMPRODUCT(--('Enroll I'!A$2:$A$2921=$B67),--('Enroll
I'!$O$2:$O$2921="NSITE0"),--('Enroll I'!M$2:$M$2921="Yes"),---('Enroll
I'!N$2:$N$2921="Yes"))
 
Yes, it does make sense. The first 3 terms of your product are either 0 or
1, depending on whether the boolean was FALSE or TRUE. The triple unary
minus for your fourth term turns FALSE to 0, and TRUE to -1.
 
Remove one of the minus signs from the last term so there are only 2 minus
signs in front of it. Just so you know what is going on, the logical test
returns a TRUE or FALSE value... to use that in the SUMPRODUCT function, the
logical expression needs to be converted to a number (1 for TRUE, 0 for
FALSE). You do this by using the logical value in a mathematical expression.
The double unary (the two minus signs) is equivalent to multiply by -1
(minus one) twice... -1 times -1 equals +1 and anything multiplied by +1
doesn't change it value.
 
Back
Top