# Help please with sumif when mutiple ranges

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

3. ## 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" <Paul.Marsh.1uo7ia_1125561904.909@excelforum-nospam.com> wrote
in message news:Paul.Marsh.1uo7ia_1125561904.909@excelforum-nospam.com...
>
> 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
>

4. ## Re: Help please with sumif when mutiple ranges

=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" <Paul.Marsh.1uo7ia_1125561904.909@excelforum-nospam.com> wrote
> in message news:Paul.Marsh.1uo7ia_1125561904.909@excelforum-nospam.com...
> >
> > 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
> >

--

Dave Peterson

5. ## Re: Help please with sumif when mutiple ranges

Thanks Dave, I usually add stuff not miss it.

Bob

"Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
news:4316EF6B.3E9CFA89@verizonXSPAM.net...
>
> =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" <Paul.Marsh.1uo7ia_1125561904.909@excelforum-nospam.com>

wrote
> > in message

news:Paul.Marsh.1uo7ia_1125561904.909@excelforum-nospam.com...
> > >
> > > 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:

> > >

>
> --
>
> Dave Peterson

6. ## Re: Help please with sumif when mutiple ranges

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

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

Bob

"Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
news:4316EF6B.3E9CFA89@verizonXSPAM.net...
>
> =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" <Paul.Marsh.1uo7ia_1125561904.909@excelforum-nospam.com>

wrote
> > in message

news:Paul.Marsh.1uo7ia_1125561904.909@excelforum-nospam.com...
> > >
> > > 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:

> > >

>
> --
>
> Dave Peterson

8. ## 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" <petersod@verizonXSPAM.net> wrote in message
> > news:4316EF6B.3E9CFA89@verizonXSPAM.net...
> > >
> > > =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"

> > <Paul.Marsh.1uo7ia_1125561904.909@excelforum-nospam.com>
> > wrote
> > > > in message

> > news:Paul.Marsh.1uo7ia_1125561904.909@excelforum-nospam.com...
> > > > >
> > > > > 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:

> > > > >
> > >
> > > --
> > >
> > > Dave Peterson

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

--

Dave Peterson

9. ## Re:Help please with sumif when mutiple ranges

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

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" <petersod@verizonXSPAM.net> wrote in message
> > news:4316EF6B.3E9CFA89@verizonXSPAM.net...
> > >
> > > =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"

> > <Paul.Marsh.1uo7ia_1125561904.909@excelforum-nospam.com>
> > wrote
> > > > in message

> > news:Paul.Marsh.1uo7ia_1125561904.909@excelforum-nospam.com...
> > > > >
> > > > > 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:

> > > > >
> > >
> > > --
> > >
> > > Dave Peterson

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

--

Dave Peterson

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

#### 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