SUMIF with multiple conditions
 

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.

SUMIF with multiple conditions
 

Hi

You need a SumProduct formula:

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

Regards,
Per

"Tim" skrev i meddelelsen
...
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.

SUMIF with multiple conditions
 

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

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

"Per Jessen" wrote:

Hi

You need a SumProduct formula:

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

Regards,
Per

"Tim" skrev i meddelelsen
...
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.

SUMIF with multiple conditions
 

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 he
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

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

Tim wrote:

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

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

"Per Jessen" wrote:

Hi

You need a SumProduct formula:

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

Regards,
Per

"Tim" skrev i meddelelsen
...
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.



--

Dave Peterson

SUMIF with multiple conditions
 

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",'She et2'!D:D,"1",'Sheet2'!L:L,B3)

Thanks again :-)


"Dave Peterson" wrote:

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 he
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

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

Tim wrote:

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

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

"Per Jessen" wrote:

Hi

You need a SumProduct formula:

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

Regards,
Per

"Tim" skrev i meddelelsen
...
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.



--

Dave Peterson

SUMIF with multiple conditions
 

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

Tim wrote:

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",'She et2'!D:D,"1",'Sheet2'!L:L,B3)

Thanks again :-)

"Dave Peterson" wrote:

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 he
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

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

Tim wrote:

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

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

"Per Jessen" wrote:

Hi

You need a SumProduct formula:

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

Regards,
Per

"Tim" skrev i meddelelsen
...
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.



--

Dave Peterson


--

Dave Peterson