SUMPRODUCT

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

  1. PAL

    PAL Guest

    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
    #1
    1. Advertisements

  2. PAL

    Jacob Skaria Guest

    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
    #2
    1. Advertisements

  3. 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
    #3
  4. 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
    #4
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Jale

    Re: sumproduct I think??

    Jale, Jul 1, 2003, in forum: Microsoft Excel Worksheet Functions
    Replies:
    0
    Views:
    524
  2. Paul Corrado

    SUMPRODUCT Adding Flexibility

    Paul Corrado, Jul 1, 2003, in forum: Microsoft Excel Worksheet Functions
    Replies:
    6
    Views:
    622
    HarlanĀ Grove
    Jul 4, 2003
  3. Lance

    SUMPRODUCT

    Lance, Jul 1, 2003, in forum: Microsoft Excel Worksheet Functions
    Replies:
    0
    Views:
    545
    Lance
    Jul 1, 2003
  4. Guest

    sumproduct? sumif(sumproduct)?

    Guest, Jul 13, 2007, in forum: Microsoft Excel Worksheet Functions
    Replies:
    3
    Views:
    652
    Guest
    Jul 13, 2007
  5. Ted M H

    Conditional SUMPRODUCT or SUMPRODUCT with Filters

    Ted M H, Aug 14, 2008, in forum: Microsoft Excel Worksheet Functions
    Replies:
    4
    Views:
    1,926
    Ted M H
    Aug 14, 2008
Loading...

Share This Page