SUMIF based on two variable matches

Petejc008

New Member
Joined
Sep 29, 2006
Messages
2
Hi!

I need to create a order report that counts all number of orders, based on a date match, and a order code match, that may appear multiple times in a separate financial report.

So, sum is based on if both of the following instances occur:

Code "PLL" appears in Sheet2 range A2:A1000
Date in Sheet1 cell D5 appears in Sheet2 range E2:E2000

If yes to both, need to add qualified values of Sheet2 range C2:C2000

I'm not that Excel savvy, so any help would be much appreciated!

Thanks!
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

plettieri

Well-known Member
Joined
Sep 4, 2002
Messages
1,556
Platform
  1. MacOS
Hi:

You might do a serach on "sumproduct" on this forum and see some examples...this sounds what you might be looking for.

plettieri
 

lenze

Legend
Joined
Feb 18, 2002
Messages
13,690
The operative word is Pivot Table. See the articles on this site reguarding PTs. Also, check out Chip's page on PTs at www.cpearson.com

lenze
 

Petejc008

New Member
Joined
Sep 29, 2006
Messages
2
Hmm, seems like the SUMPRODUCT is the way to go but I cannot find what I think I am looking for, is there any more direction you can provide to help me out? Thanks!
 

PATSYS

Well-known Member
Joined
Mar 12, 2006
Messages
1,750
Hi!

I need to create a order report that counts all number of orders, based on a date match, and a order code match, that may appear multiple times in a separate financial report.

So, sum is based on if both of the following instances occur:

Code "PLL" appears in Sheet2 range A2:A1000
Date in Sheet1 cell D5 appears in Sheet2 range E2:E2000

If yes to both, need to add qualified values of Sheet2 range C2:C2000

I'm not that Excel savvy, so any help would be much appreciated!

Thanks!

To use sumproduct:

=sumproduct((Sheet2!A2:A1000="PLL")*(Sheet2!E2:E2000=D5),C2:C2000)


To use array:

=Sum(if(Sheet2!A2:A1000="PLL"),if(Sheet2!E2:E2000=D5),C2:C2000)

with Ctrl+Shift+Enter.
 

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,728
Try...

=SUMPRODUCT(--(Sheet2!A2:A2000="PLL")--(Sheet2!E2:E2000=D5),C2:C2000)

or

=SUM(IF(Sheet2!A2:A2000="PLL",IF(Sheet2!E2:E2000=D5,C2:C2000)))

...confirmed with CONTROL+SHIFT+ENTER. Note that the ranges need to be the same size. Adjust them accordingly.

Hope this helps!
 

Forum statistics

Threads
1,141,913
Messages
5,709,306
Members
421,626
Latest member
indigochild

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top