# SUMIF based on two variable matches

#### Petejc008

##### New Member
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

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
Hi:

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

plettieri

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

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!

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.

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!

Replies
1
Views
429
Replies
19
Views
1K
Replies
2
Views
245
Replies
5
Views
126
Replies
5
Views
255

1,218,808
Messages
6,144,602
Members
450,555
Latest member
sheldor

### 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.

### Which adblocker are you using?

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

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