SumProduct within date range with two worksheets

[BrutalDawg]

I am having trouble creating a viable formula that will sum the product found on the sheet1 within a certain date range.

I would like to enable it as a vlookup. Essentially, look for item one sheet1, and total sum within the month.

Similar to below.

Sheet1:

Location	Issue	SI	Item	Time	Qty	When
Ohio	4/10/2018		123	Firm	10	4/9/2018
Ohio	4/10/2018		321	Firm	10	4/9/2018
Ohio	4/10/2018		567	Firm	10	4/9/2018
Ind	4/10/2018		123	Firm	15	4/16/2018
Ind	4/10/2018		321	Firm	15	4/16/2018
Ind	4/10/2018		567	Firm	15	4/16/2018
NO	4/10/2018		123	Forecast	10	5/1/2018
NO	4/10/2018		321	Forecast	10	5/1/2018
NO	4/10/2018		567	Forecast	10	5/1/2018

<colgroup><col><col><col span="2"><col><col><col></colgroup><tbody>
</tbody>

Sheet2 with desired results:

Item	April	May	June	Jul
123	25	10	0	0
321	25	10	0	0
567	25	10	0	0

<colgroup><col><col><col span="2"><col></colgroup><tbody>
</tbody>

formula in sheet 2 for apr,may,jun etc. would look for corresponding item on sheet1, and total the qty within that particular month.

Thanks for any help or suggestions

 

[Special-K99]

Your output is not consistent You have April May June and Jul. There is no month called Jul. It's July.
Make your data consistent or this will not work.

With Sheet2 Column A containing Item numbers and Sheet2 row 1 contaning month names try

Im assuming Sheet1 Column D is Item and you have no SI data in column C, thats what it looks like

in Sheet2!B2
=SUMPRODUCT((Sheet1!$D$1:$D$1000=Sheet2!$A2)*(TEXT(Sheet1!$B$1:$B$1000,"mmmm")=Sheet2!B$1)*(Sheet1!$F$1:$F$1000))
copy across and down

 

[steve the fish]

You also need to make sure your data doesnt cross over more than one year if you use that method.

 

[BrutalDawg]

Your output is not consistent You have April May June and Jul. There is no month called Jul. It's July.
Make your data consistent or this will not work.

With Sheet2 Column A containing Item numbers and Sheet2 row 1 contaning month names try

Im assuming Sheet1 Column D is Item and you have no SI data in column C, thats what it looks like

in Sheet2!B2
=SUMPRODUCT((Sheet1!$D$1:$D$1000=Sheet2!$A2)*(TEXT(Sheet1!$B$1:$B$1000,"mmmm")=Sheet2!B$1)*(Sheet1!$F$1:$F$1000))
copy across and down

Thanks for your help but I kept receiving a data error. I am currently trying this formula.

=SUMIFS(edi!F:F,edi!G:G,">="&Sheet1!G1,edi!G:G,"<="&G2,edi!D:D,A2)

Where A2 is the item I am looking for, g1 and g2 are beginning and end dates of the month. ie g1-4/1/2018 g2-4/30/2018 which seems to give me my desired result, but when I try and drag through, it changes to H2&H3, I3&I4, and so on. Anyone know how to lock the row, but let the column move?

 

[Special-K99]

Your dates may be a mixture of dates and text depending on the (as yet undivulged) error message

Anchor them with $ signs

=SUMIFS(edi!F:F,edi!G:G,">="&Sheet1!$G$1,edi!G:G,"<="&$G$2,edi!D:D,A2)

 

[BrutalDawg]

Your dates may be a mixture of dates and text depending on the (as yet undivulged) error message

Anchor them with $ signs

=SUMIFS(edi!F:F,edi!G:G,">="&Sheet1!$G$1,edi!G:G,"<="&$G$2,edi!D:D,A2)

Thanks Special, I had been trying to lock before the sheet designation. You are correct.