Sumif with two criteria

[Dan Perez]

I want to do a sumif() command if cell a=x AND b=y.

 

[Paul Corrado]

Dan,

=SUMPRODUCT((A1:A10="x")*(B1:B10="y")*(C1:C10))

Change the range references and criteria as needed.

PC

 

[Mark Tymes]

Paul Corrado wrote

=SUMPRODUCT((A1:A10="x")*(B1:B10="y")*(C1:C10))

Change the range references and criteria as needed.

Very good solution. One little problem I have found with this in the past.
Suppose B1:B10 contains text like as illustrated below.

boat club
banana boat
fishing boat
sailing boat

What we want to do is sum the cells containing "boat" using the same
formula. B1:B10="boat" or even B1:B10="*boat*" did not work for me.

Mark

 

[jr]

yes, the function description mentions this explicitly.
Wildcards cannot be used here.

 

[Alan Beban]

But they can here:

=SUMPRODUCT((A$1:A$5="x")*(SEARCH("*boat",B1:B5)*(C1:C5)))

Alan Beban

 

[Alan Beban]

What's in I9 and K9?

Alan Beban

Try this, must be entered as an array...

{=SUM(IF((I13:I21=I9)*(K13:K21=K9),M13:M21,0))}

adjust range...

 

[Mark Tymes]

jr wrote

yes, the function description mentions this explicitly.
Wildcards cannot be used here.

I was unaware of this but I do believe it must be true. Thank you.

Mark

 

[Alan Beban]

I posted the following in this thread yesterday:

=SUMPRODUCT((A$1:A$5="x")*(SEARCH("*boat",B1:B5)*(C1:C5)))

Alan Beban