SUMIF based on two variable matches

[Petejc008]

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!

 

[plettieri]

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]

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]

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]

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]

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!