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"))

Check the last condition "---" instead of "--"

=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"))

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

If this post helps click Yes
---------------
Jacob Skaria


"PAL" wrote:

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.
--
David Biddulph

"PAL" wrote in message
...
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"))

OK, How to fix?

"David Biddulph" wrote:

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.
--
David Biddulph

"PAL" wrote in message
...
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"))

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.

--
Rick (MVP - Excel)


"PAL" wrote in message
...
OK, How to fix?

"David Biddulph" wrote:

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.
--
David Biddulph

"PAL" wrote in message
...
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"))