Help please with sumif when mutiple ranges

**Paul Marsh** · 09-01-2005, 03:16 AM

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 Sheppard** · 09-01-2005, 03:52 AM

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

Bob Phillips · 09-01-2005, 06:05 AM

=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: http://www.excelforum.com/showthread...hreadid=401054
>

Dave Peterson · 09-01-2005, 09:05 AM

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" <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: http://www.excelforum.com/showthread...hreadid=401054
> >

--

Dave Peterson

Bob Phillips · 09-01-2005, 10:05 AM

Thanks Dave, I usually add stuff not miss it.

Bob

"Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
news:4316EF6B.3E9CFA89@verizonXSPAM.net...
> 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" <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:
http://www.excelforum.com/showthread...hreadid=401054
> > >
>
> --
>
> Dave Peterson

**Paul Marsh** · 09-01-2005, 08:38 PM

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

**Paul Marsh** · 09-01-2005, 08:48 PM

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...
> 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" <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:
http://www.excelforum.com/showthread...hreadid=401054
> > >
>
> --
>
> Dave Peterson

Dave Peterson · 09-01-2005, 10:05 PM

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...
> > > 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"
> > <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:
> > 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

**Paul Marsh** · 09-01-2005, 10:12 PM

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...
> > > 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"
> > <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:
> > 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