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!
 

Some videos you may like

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

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,749
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,379
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,114,051
Messages
5,545,726
Members
410,702
Latest member
clizama18
Top