SUMPRODUCT

Discussion in 'Microsoft Excel Worksheet Functions' started by PAL, Aug 3, 2009.

1. PALGuest

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

PAL, Aug 3, 2009

2. Jacob SkariaGuest

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

Jacob Skaria, Aug 3, 2009

3. David BiddulphGuest

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
news:...
> 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"))

David Biddulph, Aug 3, 2009
4. Rick RothsteinGuest

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
news:...
> 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
>> news:...
>> > 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"))

>>
>>
>>

Rick Rothstein, Aug 3, 2009