PC Review


Reply
 
 
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"))
 
Reply With Quote
 
 
 
 
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"))

 
Reply With Quote
 
 
 
 
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"))



 
Reply With Quote
 
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"))

>>
>>
>>


 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

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 Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Sumproduct instead of SumifS in VBA (application.sumproduct) Majken Bilslev-Jensen Microsoft Excel Programming 7 30th Dec 2010 06:56 PM
Sumproduct with Condition OR Sumproduct with ADDRESS function - HE gholly Microsoft Excel Misc 2 28th Sep 2009 05:07 PM
Conditional SUMPRODUCT or SUMPRODUCT with Filters Ted M H Microsoft Excel Worksheet Functions 4 14th Aug 2008 07:50 PM
sumproduct? sumif(sumproduct)? =?Utf-8?B?RGF2aWQ=?= Microsoft Excel Worksheet Functions 3 13th Jul 2007 07:06 PM
SUMPRODUCT - NO SUMPRODUCT! =?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 06:13 PM.