SUMIF with multiple conditions

[Tim]

I am using Excel 2010 to track purchases. I have 2 sheets in one workbook
and need to do a SUMIF that has 3 conditions.

Condition 1 is Status=â€processed†(Column B)
Condition 2 is Quarter (1,2,3 or 4) (Column D)
Condition 3 is Line Item (“Item1â€, “Item2â€, etc.) (Column L)

The dollar amount is in Column J.


If the conditions on sheet2 are met then it will sum the dollar amount in
Sheet1.

How can I do this?

thank you.

 

[Per Jessen]

Hi

You need a SumProduct formula:

=SUMPRODUCT(--(Sheet2!B2:B1000="processed"),--(Sheet2!D21000=1),--(Sheet2!L2:L1000="Item1"),Sheet2!J2:J1000)

Regards,
Per

 

[Tim]

I'm sorry, but what the -- for?

Do I use + between each () argument or just ,?

 

[Dave Peterson]

If your list separator is the comma character (common in the USA), you'll use
the formula as written--no changes.

If your list separator is something else (a semicolon is common in other parts
of the world), then you'd use a semicolon ( instead of the comma.

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

 

[Tim]

I want to thank folks for their replies.

I was digging into trying to figure out how to make sumproduct work when I
ran acors SUMIFS and as it turns out that did exactly what I needed done.

Here's a sample of the SUMIFS function I used in my spreadsheet
=SUMIFS('Sheet2'!J:J,'Sheet2'!B:B,"Processed",'Sheet2'!D,"1",'Sheet2'!L:L,B3)

Thanks again

 

[Dave Peterson]

=sumifs() was added in xl2007. If you have to revert to xl2003 or earlier,
you'll want to remember the =sumproduct() function.