+ Reply to Thread
Results 1 to 9 of 9

Help please with sumif when mutiple ranges

  1. #1
    Registered User
    Join Date
    03-18-2004
    Location
    Western Australia
    Posts
    4

    Cool Help please with sumif when mutiple ranges

    Could someone please help me sum the contents of one column, only when they match the criteria from two other columns. Crude example; sumif A:A=Red AND B:B=Pink, sum C:C

  2. #2
    Valued Forum Contributor
    Join Date
    06-30-2005
    Location
    Verwood, Dorset, England
    MS-Off Ver
    Excel 2000
    Posts
    479
    Quote Originally Posted by Paul Marsh
    Could someone please help me sum the contents of one column, only when they match the criteria from two other columns. Crude example; sumif A:A=Red AND B:B=Pink, sum C:C
    Hi Paul

    Try this > =SUM(IF(A:A="Red",IF(B:B="Pink",C:C,0),0))
    Paul

  3. #3
    Bob Phillips
    Guest

    Re: Help please with sumif when mutiple ranges

    =SUMPRODUCT(-(A2:A2000="Red"),--(B2:B2000="Pink"),C2:C2000)

    --

    HTH

    RP
    (remove nothere from the email address if mailing direct)


    "Paul Marsh" <[email protected]> wrote
    in message news:[email protected]...
    >
    > Could someone please help me sum the contents of one column, only when
    > they match the criteria from two other columns. Crude example; sumif
    > A:A=Red AND B:B=Pink, sum C:C
    >
    >
    > --
    > Paul Marsh
    > ------------------------------------------------------------------------
    > Paul Marsh's Profile:

    http://www.excelforum.com/member.php...fo&userid=7310
    > View this thread: http://www.excelforum.com/showthread...hreadid=401054
    >




  4. #4
    Dave Peterson
    Guest

    Re: Help please with sumif when mutiple ranges

    typo alert!

    =SUMPRODUCT(--(A2:A2000="Red"),--(B2:B2000="Pink"),C2:C2000)

    (There are two minus signs in front of the that first argument.)

    Bob Phillips wrote:
    >
    > =SUMPRODUCT(-(A2:A2000="Red"),--(B2:B2000="Pink"),C2:C2000)
    >
    > --
    >
    > HTH
    >
    > RP
    > (remove nothere from the email address if mailing direct)
    >
    > "Paul Marsh" <[email protected]> wrote
    > in message news:[email protected]...
    > >
    > > Could someone please help me sum the contents of one column, only when
    > > they match the criteria from two other columns. Crude example; sumif
    > > A:A=Red AND B:B=Pink, sum C:C
    > >
    > >
    > > --
    > > Paul Marsh
    > > ------------------------------------------------------------------------
    > > Paul Marsh's Profile:

    > http://www.excelforum.com/member.php...fo&userid=7310
    > > View this thread: http://www.excelforum.com/showthread...hreadid=401054
    > >


    --

    Dave Peterson

  5. #5
    Bob Phillips
    Guest

    Re: Help please with sumif when mutiple ranges

    Thanks Dave, I usually add stuff not miss it.

    Bob

    "Dave Peterson" <[email protected]> wrote in message
    news:[email protected]...
    > typo alert!
    >
    > =SUMPRODUCT(--(A2:A2000="Red"),--(B2:B2000="Pink"),C2:C2000)
    >
    > (There are two minus signs in front of the that first argument.)
    >
    > Bob Phillips wrote:
    > >
    > > =SUMPRODUCT(-(A2:A2000="Red"),--(B2:B2000="Pink"),C2:C2000)
    > >
    > > --
    > >
    > > HTH
    > >
    > > RP
    > > (remove nothere from the email address if mailing direct)
    > >
    > > "Paul Marsh" <[email protected]>

    wrote
    > > in message

    news:[email protected]...
    > > >
    > > > Could someone please help me sum the contents of one column, only when
    > > > they match the criteria from two other columns. Crude example; sumif
    > > > A:A=Red AND B:B=Pink, sum C:C
    > > >
    > > >
    > > > --
    > > > Paul Marsh

    > >

    > ------------------------------------------------------------------------
    > > > Paul Marsh's Profile:

    > > http://www.excelforum.com/member.php...fo&userid=7310
    > > > View this thread:

    http://www.excelforum.com/showthread...hreadid=401054
    > > >

    >
    > --
    >
    > Dave Peterson




  6. #6
    Registered User
    Join Date
    03-18-2004
    Location
    Western Australia
    Posts
    4

    Re: Help please with sumif when mutiple ranges

    Quote Originally Posted by Paul Sheppard
    Hi Paul

    Try this > =SUM(IF(A:A="Red",IF(B:B="Pink",C:C,0),0))
    Thanks Paul - couldn't get it to work but others on line have offered an alternative solution using =SUMPRODUCT(--(A2:A2000="Red"),--(B2:B2000="Pink"),C2:C2000)
    Thanks again

  7. #7
    Registered User
    Join Date
    03-18-2004
    Location
    Western Australia
    Posts
    4

    Re: Help please with sumif when mutiple ranges

    Thanks Bob and Dave that works - couple of points I would like clarifying if possibe;

    1. Having single minus signs in both cases returns the same result as having both double minus signs - is this wrong for any reason?

    2. Am I right in saying that I have to specify a specific range when using SUMPRODUCT. My application has various number of rows and my prefererence is to use SUMIF if possible to allow selection of the whole column (A:A). Any way to achieve this?

    Either way you guys have solved my immediate problem and I am very grateful.
    Thank you once again.

    Cheers

    Paul



    Quote Originally Posted by Bob Phillips
    Thanks Dave, I usually add stuff not miss it.

    Bob

    "Dave Peterson" <[email protected]> wrote in message
    news:[email protected]...
    > typo alert!
    >
    > =SUMPRODUCT(--(A2:A2000="Red"),--(B2:B2000="Pink"),C2:C2000)
    >
    > (There are two minus signs in front of the that first argument.)
    >
    > Bob Phillips wrote:
    > >
    > > =SUMPRODUCT(-(A2:A2000="Red"),--(B2:B2000="Pink"),C2:C2000)
    > >
    > > --
    > >
    > > HTH
    > >
    > > RP
    > > (remove nothere from the email address if mailing direct)
    > >
    > > "Paul Marsh" <[email protected]>

    wrote
    > > in message

    news:[email protected]...
    > > >
    > > > Could someone please help me sum the contents of one column, only when
    > > > they match the criteria from two other columns. Crude example; sumif
    > > > A:A=Red AND B:B=Pink, sum C:C
    > > >
    > > >
    > > > --
    > > > Paul Marsh

    > >

    > ------------------------------------------------------------------------
    > > > Paul Marsh's Profile:

    > > http://www.excelforum.com/member.php...fo&userid=7310
    > > > View this thread:

    http://www.excelforum.com/showthread...hreadid=401054
    > > >

    >
    > --
    >
    > Dave Peterson

  8. #8
    Dave Peterson
    Guest

    Re: Help please with sumif when mutiple ranges

    The -- is used convert Trues and falses to 1/0's.

    The first minus changes true to -1, the second one makes it +1.

    Because you had an even number of arguments, you actually just multiplied -1 by
    -1 to get +1.

    You could either keep track of how many arguments you need and always keep an
    even number of single -'s or being more careful, always use --.

    If you always use --, then if you add another argument, you don't have to worry
    about how many you've used before.

    =SUMPRODUCT(-(A2:A2000="Red"),-(B2:B2000="Pink"),
    -(d2:d2000="balloon"),C2:C2000)

    Would end up with the wrong sign (if the results weren't 0).



    Paul Marsh wrote:
    >
    > Thanks Bob and Dave that works - couple of points I would like
    > clarifying if possibe;
    >
    > 1. Having single minus signs in both cases returns the same result as
    > having both double minus signs - is this wrong for any reason?
    >
    > 2. Am I right in saying that I have to specify a specific range when
    > using SUMPRODUCT. My application has various number of rows and my
    > prefererence is to use SUMIF if possible to allow selection of the
    > whole column (A:A). Any way to achieve this?
    >
    > Either way you guys have solved my immediate problem and I am very
    > grateful.
    > Thank you once again.
    >
    > Cheers
    >
    > Paul
    >
    > Bob Phillips Wrote:
    > > Thanks Dave, I usually add stuff not miss it.
    > >
    > > Bob
    > >
    > > "Dave Peterson" <[email protected]> wrote in message
    > > news:[email protected]...
    > > > typo alert!
    > > >
    > > > =SUMPRODUCT(--(A2:A2000="Red"),--(B2:B2000="Pink"),C2:C2000)
    > > >
    > > > (There are two minus signs in front of the that first argument.)
    > > >
    > > > Bob Phillips wrote:
    > > > >
    > > > > =SUMPRODUCT(-(A2:A2000="Red"),--(B2:B2000="Pink"),C2:C2000)
    > > > >
    > > > > --
    > > > >
    > > > > HTH
    > > > >
    > > > > RP
    > > > > (remove nothere from the email address if mailing direct)
    > > > >
    > > > > "Paul Marsh"

    > > <[email protected]>
    > > wrote
    > > > > in message

    > > news:[email protected]...
    > > > > >
    > > > > > Could someone please help me sum the contents of one column, only

    > > when
    > > > > > they match the criteria from two other columns. Crude example;

    > > sumif
    > > > > > A:A=Red AND B:B=Pink, sum C:C
    > > > > >
    > > > > >
    > > > > > --
    > > > > > Paul Marsh
    > > > >
    > > >

    > > ------------------------------------------------------------------------
    > > > > > Paul Marsh's Profile:
    > > > > http://www.excelforum.com/member.php...fo&userid=7310
    > > > > > View this thread:

    > > http://www.excelforum.com/showthread...hreadid=401054
    > > > > >
    > > >
    > > > --
    > > >
    > > > Dave Peterson

    >
    > --
    > Paul Marsh
    > ------------------------------------------------------------------------
    > Paul Marsh's Profile: http://www.excelforum.com/member.php...fo&userid=7310
    > View this thread: http://www.excelforum.com/showthread...hreadid=401054


    --

    Dave Peterson

  9. #9
    Registered User
    Join Date
    03-18-2004
    Location
    Western Australia
    Posts
    4

    Re:Help please with sumif when mutiple ranges

    Thanks Dave - very helpful, double - is obviously the way to go! Thanks again
    Paul


    Quote Originally Posted by Dave Peterson
    The -- is used convert Trues and falses to 1/0's.

    The first minus changes true to -1, the second one makes it +1.

    Because you had an even number of arguments, you actually just multiplied -1 by
    -1 to get +1.

    You could either keep track of how many arguments you need and always keep an
    even number of single -'s or being more careful, always use --.

    If you always use --, then if you add another argument, you don't have to worry
    about how many you've used before.

    =SUMPRODUCT(-(A2:A2000="Red"),-(B2:B2000="Pink"),
    -(d2:d2000="balloon"),C2:C2000)

    Would end up with the wrong sign (if the results weren't 0).



    Paul Marsh wrote:
    >
    > Thanks Bob and Dave that works - couple of points I would like
    > clarifying if possibe;
    >
    > 1. Having single minus signs in both cases returns the same result as
    > having both double minus signs - is this wrong for any reason?
    >
    > 2. Am I right in saying that I have to specify a specific range when
    > using SUMPRODUCT. My application has various number of rows and my
    > prefererence is to use SUMIF if possible to allow selection of the
    > whole column (A:A). Any way to achieve this?
    >
    > Either way you guys have solved my immediate problem and I am very
    > grateful.
    > Thank you once again.
    >
    > Cheers
    >
    > Paul
    >
    > Bob Phillips Wrote:
    > > Thanks Dave, I usually add stuff not miss it.
    > >
    > > Bob
    > >
    > > "Dave Peterson" <[email protected]> wrote in message
    > > news:[email protected]...
    > > > typo alert!
    > > >
    > > > =SUMPRODUCT(--(A2:A2000="Red"),--(B2:B2000="Pink"),C2:C2000)
    > > >
    > > > (There are two minus signs in front of the that first argument.)
    > > >
    > > > Bob Phillips wrote:
    > > > >
    > > > > =SUMPRODUCT(-(A2:A2000="Red"),--(B2:B2000="Pink"),C2:C2000)
    > > > >
    > > > > --
    > > > >
    > > > > HTH
    > > > >
    > > > > RP
    > > > > (remove nothere from the email address if mailing direct)
    > > > >
    > > > > "Paul Marsh"

    > > <[email protected]>
    > > wrote
    > > > > in message

    > > news:[email protected]...
    > > > > >
    > > > > > Could someone please help me sum the contents of one column, only

    > > when
    > > > > > they match the criteria from two other columns. Crude example;

    > > sumif
    > > > > > A:A=Red AND B:B=Pink, sum C:C
    > > > > >
    > > > > >
    > > > > > --
    > > > > > Paul Marsh
    > > > >
    > > >

    > > ------------------------------------------------------------------------
    > > > > > Paul Marsh's Profile:
    > > > > http://www.excelforum.com/member.php...fo&userid=7310
    > > > > > View this thread:

    > > http://www.excelforum.com/showthread...hreadid=401054
    > > > > >
    > > >
    > > > --
    > > >
    > > > Dave Peterson

    >
    > --
    > Paul Marsh
    > ------------------------------------------------------------------------
    > Paul Marsh's Profile: http://www.excelforum.com/member.php...fo&userid=7310
    > View this thread: http://www.excelforum.com/showthread...hreadid=401054


    --

    Dave Peterson

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1