SUMIF for two or more columns

[Randy]

I need help using SUMIF where one value is looked for in
column A and three values in column B. I would like to
have SUMIF look into A for one value and into B for three
(or more) values, then sum whatever it finds in C. For
example, I want to add C where A equals code 5437 and B
equals either code 000100 or 000120 or 000130. The answer
would be 80. Thanks.


5437 000100 10
5437 000120 20
5437 000130 50
5437 999910 40
5437 999920 80
5438 000100 60
5438 000120 90

 

[Frank Kabel]

Hi
try
=SUMPRODUCT((A1:A100=5437)*(B1:B100={"000100","000120","000130"})*(C1:C
100))

 

[Randy]

Just out of curiosity, Frank, doesn't the * mean that the
items are all multiplied together, if I understand
correctly? Would that still give me the sum I need? I am a
little confused.

 

[Frank Kabel]

Hi
only boolean values are multiplied. See for an explanation:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

 

[Aladin Akyurek]

Since you have 2 conditions, a SumIf formula will not apply. Try the
following instead:

=SUMPRODUCT(--($A$2:$A$100=5437),--ISNUMBER(MATCH($B$2:$B$100,{"000100","000
120","000130"},0)),$C$2:$C$100)

You can also enter the conditions into cells of their own and reference
these cells in the foregoing formula:

=SUMPRODUCT(--($A$2:$A$100=E2),--ISNUMBER(MATCH($B$2:$B$100,$F$2:$H$2,0)),$C
$2:$C$100)

where E2 houses a code like 5437 and F2:H2 codes like 000100, 000100, and
000130.

For more info on formulas that operate on computed arrays, see:

http://www.mrexcel.com/wwwboard/messages/8961.html (on SumProduct)

http://www.emailoffice.com/excel/arrays-bobumlas.html (on formulas confirmed
with control+shift+enter)