Thread Tools Rate Thread

# SUMPRODUCT

PAL
Guest
Posts: n/a

 3rd Aug 2009
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
Guest
Posts: n/a

 3rd Aug 2009
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"))

David Biddulph
Guest
Posts: n/a

 3rd Aug 2009
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:(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"))

Rick Rothstein
Guest
Posts: n/a

 3rd Aug 2009
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:(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:(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"))

>>
>>
>>

 Thread Tools Rate This Thread Rate This Thread: 5 : Excellent 4 : Good 3 : Average 2 : Bad 1 : Terrible

 Posting Rules You may not post new threads You may not post replies You may not post attachments You may not edit your posts BB code is On Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are Off Forum Rules

 Similar Threads Thread Thread Starter Forum Replies Last Post Majken Bilslev-Jensen Microsoft Excel Programming 7 30th Dec 2010 06:56 PM gholly Microsoft Excel Misc 2 28th Sep 2009 05:07 PM Ted M H Microsoft Excel Worksheet Functions 4 14th Aug 2008 07:50 PM =?Utf-8?B?RGF2aWQ=?= Microsoft Excel Worksheet Functions 3 13th Jul 2007 07:06 PM =?Utf-8?B?TWFyaw==?= Microsoft Excel Programming 3 23rd Nov 2005 03:36 PM

Features

Advertising

Newsgroups

All times are GMT +1. The time now is 04:43 AM.