SumIf Question

[Guest]

Hi

Can anyone tell me if the SumIf function can work with 2 different sets of
criteria.

eg:

A1 = P B1 = M C1 = 20
A2 = P B2 = M C2 = 10
A3 = U B3 = M C2 = 10

I would like a formula to sum the C column numbers based on if the A column
returns the letter U and the B column returns the letter M.

But return a zero if the A column returns the letter P and the B column
returns the letter M.

I can get half way there using the SumIf function but I can't get a 2nd
criteria in the formula.

I run Excel 2000

Any help would be much appreciated

 

[Peo Sjoblom]

=SUMPRODUCT(--(A1:A3="U"),--(B1:B3="M"),C1:C30)

--
Regards,

Peo Sjoblom

(No private emails please)

 

[Guest]

Thanks Peo but I just could not get this to work for some reason

Also I have never seen -- placed in a formula, what does this do?


John

 

[Bob Phillips]

John,

If it doesn't work, it usually means there are extraneous spaces in column A
or B, i.e. they are not just Us and Ms, or column C is text.

Try doing a SUMIF on A and C and B and C and see if that works okay.

For the --, see http://www.xldynamic.com/source/xld.SUMPRODUCT.html

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Jerry W. Lewis]

There is a typeo in Peo's formula. Each array should be the same size
(either all end at row 3 or all end at row 30).

An alternative to -- for coercing the logical values to 1's and 0's so
that SUMPRODUCT will work with them is
=SUMPRODUCT((A1:A3="U")*(B1:B3="M"),C1:C3)
but this will return exactly the same result as the corrected version of
Peo's formula.

If there is also issue is that the "values" in C are text instead of
numbers, then
=SUMPRODUCT((A1:A3="U")*(B1:B3="M")*C1:C3)
will also coerce column C (but will return an error if some values are
not coercible.

If columns A or B have trailing spaces, then
=SUMPRODUCT((LEFT(A1:A3,1)="U")*(LEFT(B1:B3,1)="M")*C1:C3)
will take care of that.

Jerry

 

[Guest]

Thanks Guys ! - works great !!!

There is a typeo in Peo's formula. Each array should be the same size
(either all end at row 3 or all end at row 30).

An alternative to -- for coercing the logical values to 1's and 0's so
that SUMPRODUCT will work with them is
=SUMPRODUCT((A1:A3="U")*(B1:B3="M"),C1:C3)
but this will return exactly the same result as the corrected version of
Peo's formula.

If there is also issue is that the "values" in C are text instead of
numbers, then
=SUMPRODUCT((A1:A3="U")*(B1:B3="M")*C1:C3)
will also coerce column C (but will return an error if some values are
not coercible.

If columns A or B have trailing spaces, then
=SUMPRODUCT((LEFT(A1:A3,1)="U")*(LEFT(B1:B3,1)="M")*C1:C3)
will take care of that.

Jerry

 

[Jerry W. Lewis]

You're welcome.

Jerry

Thanks Guys ! - works great !!!

: