Help please with sumif when mutiple ranges

P

Paul Marsh

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

Paul Sheppard

Paul said:
Could someone please help me sum the contents of one column, only whe
they match the criteria from two other columns. Crude example; sumi
A:A=Red AND B:B=Pink, sum C:C
Click to expand...

Hi Paul

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

Bob Phillips

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

--

HTH

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

Dave Peterson

typo alert!

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

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

Paul Marsh

Paul said:
Hi Paul

Try this > =SUM(IF(A:A="Red",IF(B:B="Pink",C:C,0),0))
Click to expand...

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
 
P

Paul Marsh

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
 
D

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

Paul Marsh

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