D
Dan Perez
I want to do a sumif() command if cell a=x AND b=y.
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
P
Paul Corrado
Dan,
=SUMPRODUCT((A1:A10="x")*(B1:B10="y")*(C1:C10))
Change the range references and criteria as needed.
PC
=SUMPRODUCT((A1:A10="x")*(B1:B10="y")*(C1:C10))
Change the range references and criteria as needed.
PC
M
Mark Tymes
Paul Corrado wrote
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
=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
J
jr
yes, the function description mentions this explicitly.
Wildcards cannot be used here.
Wildcards cannot be used here.
A
Alan Beban
But they can here:
=SUMPRODUCT((A$1:A$5="x")*(SEARCH("*boat",B1:B5)*(C1:C5)))
Alan Beban
=SUMPRODUCT((A$1:A$5="x")*(SEARCH("*boat",B1:B5)*(C1:C5)))
Alan Beban
A
Alan Beban
What's in I9 and K9?
Alan Beban
Alan Beban
Try this, must be entered as an array...
{=SUM(IF((I13:I21=I9)*(K13:K21=K9),M13:M21,0))}
adjust range...
M
Mark Tymes
jr wrote
I was unaware of this but I do believe it must be true. Thank you.
Mark
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
A
Alan Beban
I posted the following in this thread yesterday:
=SUMPRODUCT((A$1:A$5="x")*(SEARCH("*boat",B1:B5)*(C1:C5)))
Alan Beban
=SUMPRODUCT((A$1:A$5="x")*(SEARCH("*boat",B1:B5)*(C1:C5)))
Alan Beban