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" <(E-Mail Removed)> wrote in message
news:B0939A63-1013-4749-9954-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
news:7240ED4D-CD4E-4393-9078-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
>> news:B0939A63-1013-4749-9954-(E-Mail Removed)...
>> > 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"))
>>
>>
>>