How do I ask for multiple criteria when creating a "sumif" formul.

[Guest]

I need to create a "sumif" formula in excel that allows me to dictate
MULTIPLE criteria...for example, in range a1:c10, I want to sum cell c3 only
if a1="value" and b1="value", and so on down the line...ideally, I would
creat a formula that looks like this:

=sumif(a1:c10,a1:a10="value1"AND b1:b10="value2",c1:c10)

I've tried layer "if" and "and" formulas in the sumif formula; nothing seems
to work.

HELP!

 

[Dave R.]

You can use sumproduct for that:

=SUMPRODUCT((A1:A4="red")*(B1:B4="car"),C1:C4)

this will sum C1:C4 when A1:A4="red" and B1:B4="car".

 

[Guest]

I'm not sure I understand the wording of your question, but it sounds like
SUMPRODUCT could help you:
=SUMPRODUCT(--(A1:A10="Required Text"),--(B1:B10=RequiredNumber),(C1:C10))

This formula should add the values in Column C only if the text in the same
row in column A matches the RequiredText in the formula and the value in the
same row for column B matches the RequiredNumber.
Note that text goes in quotes, numbers do not.

For more, check out:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html


tj

 

[JE McGimpsey]

One way:

=SUMPRODUCT(--(A1:A10="value1"),--(B1:B10="value2"),C1:C10)


for an explanation of "--", see

http://www.mcgimpsey.com/excel/doubleneg.html