Sum Product Formula + Rolling months update

[natheplas]

Hi All,

I'm really struggling with a formula I need to work out, not sure it can be done?

I have a very short deadline with this, all help appreciated.

The formula I have put together is the following

=SUMPRODUCT(--(Table_Query_from_******_Ltd3[STOCK_CODE]=A8), --(Table_Query_from_******_Ltd3[Date]="Oct-2017"), Table_Query_from_******_Ltd3[QTY_ORDER])

This gives me a total for each product on order for each month.

For example if we are selling 10 apples over 5 different orders in Oct 2017. It would say 10 in that cell.

This works well and is what I need, but I now need to take it further.

I have another worksheet with historical data on it like below:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Stock Code[/TD]
[TD]Qty Ordered[/TD]
[TD]Order Number[/TD]
[TD]Month-Year[/TD]
[/TR]
[TR]
[TD]apple[/TD]
[TD]50[/TD]
[TD]151515[/TD]
[TD]Oct-17[/TD]
[/TR]
[TR]
[TD]apple[/TD]
[TD]100[/TD]
[TD]959595[/TD]
[TD]Oct-16[/TD]
[/TR]
</tbody>[/TABLE]

How can I use a sumproduct or sumifs formula, or any formula to say in that in cell B2 below ???? it scans the worksheet above for apple, but only adds together qty ordered within the last 12 months, using todays date (so this will change as the year goes on and the sheet above is updated).


[TABLE="width: 500"]
<tbody>[TR]
[TD]Product Code[/TD]
[TD]12 Months[/TD]
[TD]6 weeks average of 12 months[/TD]
[TD]6 months[/TD]
[TD] 6 weeks average of 6 months[/TD]
[TD]3 months[/TD]
[TD]6 weeks average of 3 months[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]apple[/TD]
[TD]????[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]banana[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]orange[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Please let me know if you need anymore information for this one.

I dont know if its possible, but I need to to use a formula to add together qty ordered, over a changing date range for each product code.

I need a formula for 12 months, 6 months and 3 months. I can work out 6 weeks of that.

Thanks

Natheplas

 

[AhoyNC]

Maybe something like this.

Excel Workbook
	A	B	C	D	E	F
1	Stock Code	Qty Ordered	Order Number	Month-Year
2	apple	50	151515	17-Oct
3	apple	100	959595	16-Jan
4	banana	15	88888	17-Sep
5	apple	25	87777	17-Feb
6
7
8
9
10	Product Code	12 Months		6 months		3 months
11	apple	75		50		50
12	banana	15		25		25
13	orange	0		0		0
Sheet

 

[natheplas]

Hi AhoyNC,

Thank you so much for coming back to me, I'm still doing something wrong. Hopefully you can show me.

I've tried it with the first one and It's just returning 0, which I know isn't correct.

=SUMIFS(C34109:C109122,$A$34109:$A$109122,A34109,$F$34109:$F$109122,">="&EOMONTH(TODAY(),-13)+1)

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]C[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Stock Code[/TD]
[TD]QTY Ordered[/TD]
[TD]Order Number[/TD]
[TD]Month-Year[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Apple[/TD]
[TD]1[/TD]
[TD]1111[/TD]
[TD]Oct-2017[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Apple[/TD]
[TD]12[/TD]
[TD]2222[/TD]
[TD]Dec-2016[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Apple [/TD]
[TD]3[/TD]
[TD]3333[/TD]
[TD]April-2017[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Apple[/TD]
[TD]5[/TD]
[TD]4444[/TD]
[TD]Jan-2017[/TD]
[/TR]
</tbody>[/TABLE]

I'm not sure If I can post the file on this or send it to you somehow. It would be great to have a resolution for this tonight.

Thank you.

Natheplas

 

[AhoyNC]

Is your Month-Year column an actual Excel date or text?

 

[natheplas]

Its pulled from a date column using =TEXT([@[DESPATCH_DATE]],"mmm-yyyy").

 

[natheplas]

Ah ha! I see what you were getting at. Thank you so much for your help! Have a great day/eve!

Natheplas

 

[AhoyNC]

You're welcome. You could use Custom Formatting to show your date column as mmm-yyy instead of making it TEXT.