Rolling 12 month forecast - backwards

[yorkam]

Help, I'm looking for a formula to calculate a rolling backwards forecast with actuals, based on a date field to calculate a rolling 12 month total. I'd like to automate it based on the current date so should take last twelve months from today.

For example:
Month1 Month 2 Month 3...... to month 12 and then next month add in month 13
100 120 100
So for the first month the total will be the sum of month 1 to 12
next month the value will be month 2 to 13
next month value will be month 3 to 14

I need it to be a formula in a cell and not VBA.
I can do it via a sumif on the basis that someone manually enters it a condition and takes the condition out the next period, but do not want to rely on individuals, so any way to automate would greatly be appreciated.
thanks in advance

 

[Eric W]

How about:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1	Date	1/1/2020	2/1/2020	3/1/2020	4/1/2020	5/1/2020	6/1/2020	7/1/2020	8/1/2020	9/1/2020	10/1/2020	11/1/2020	12/1/2020	1/1/2021	2/1/2021	3/1/2021	4/1/2021	5/1/2021	6/1/2021	7/1/2021	8/1/2021
2	Value	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18
3
4	Last 12 months	150
Sheet2

Cell Formulas
Range	Formula
B4	B4	=SUMIFS(B2:ZZ2,B1:ZZ1,">="&EOMONTH(TODAY(),-12),B1:ZZ1,"<="&EOMONTH(TODAY(),0))

 

[Massoud]

How about:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1	Date	1/1/2020	2/1/2020	3/1/2020	4/1/2020	5/1/2020	6/1/2020	7/1/2020	8/1/2020	9/1/2020	10/1/2020	11/1/2020	12/1/2020	1/1/2021	2/1/2021	3/1/2021	4/1/2021	5/1/2021	6/1/2021	7/1/2021	8/1/2021
2	Value	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18
3
4	Last 12 months	150
Sheet2

Cell Formulas
Range	Formula
B4	B4	=SUMIFS(B2:ZZ2,B1:ZZ1,">="&EOMONTH(TODAY(),-12),B1:ZZ1,"<="&EOMONTH(TODAY(),0))

Hi Eric
I had the same question and found this on a google search.
Your formula works great.
How do I replace "EOMONTH(TODAY()" with a cell reference instead? Let's say I want the rolling 12 but starting last month going back 12 and not this month. The referenced call will say 4/30/2021

 

[Eric W]

Welcome to the forum!

Just replace TODAY() with your cell reference. If it's A1, the formula would be:

=SUMIFS(B2:ZZ2,B1:ZZ1,">="&EOMONTH(A1,-12),B1:ZZ1,"<="&EOMONTH(A1,0))

 

[Massoud]

=SUMIFS(B2:ZZ2,B1:ZZ1,">="&EOMONTH(A1,-12),B1:ZZ1,"<="&EOMONTH(A1,0))

Thank you, Eric. It worked and I appreciate the help.

 

[CindaH]

I don't think the formula is giving me the correct answer. Here's my data:

Date	01-Jan-20	01-Feb-20	01-Mar-20	01-Apr-20	01-May-20	01-Jun-20	01-Jul-20	01-Aug-20	01-Sep-20	01-Oct-20	01-Nov-20	01-Dec-20	01-Jan-21	01-Feb-21	01-Mar-21	01-Apr-21	01-May-21	01-Jun-21	01-Jul-21	01-Aug-21	01-Sep-21	01-Oct-21	01-Nov-21	01-Dec-21
Units	94.25	89.25	88.50	105.00	89.00	88.50	86.00	78.00	68.50	68.00	65.00	78.00	73.25	89.00	106.25	101.25	86.00	56.75	97.25	61.00	80.25
Rolling 12 Months	962.00	962.00	962.00	962.00	962.00	962.00	962.00	962.00	962.00	962.00	894.00	829.00	751.00	677.75	588.75	482.50	381.25	295.25	238.50	141.25	80.25	0.00	0.00	0.00

For example, 01-Feb-21 says 677.75, but Feb-20 through Jan-21 should be 977. What am I missing?

Thanks!

 

[Eric W]

Welcome to the MrExcel forum!

Just as an FYI, your question is more likely to be seen and answered if you open a new thread.

I can't tell what's off with your formula, since you didn't say what it is. But consider this:

Book1 (version 1).xlsb
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y
1	Date	1-Jan-20	1-Feb-20	1-Mar-20	1-Apr-20	1-May-20	1-Jun-20	1-Jul-20	1-Aug-20	1-Sep-20	1-Oct-20	1-Nov-20	1-Dec-20	1-Jan-21	1-Feb-21	1-Mar-21	1-Apr-21	1-May-21	1-Jun-21	1-Jul-21	1-Aug-21	1-Sep-21	1-Oct-21	1-Nov-21	1-Dec-21
2	Units	94.25	89.25	88.5	105	89	88.5	86	78	68.5	68	65	78	73.25	89	106.25	101.25	86	56.75	97.25	61	80.25
3
4	Rolling 12 Months	998	977	976.75	994.5	990.75	987.75	956	967.25	950.25	962	894	829	751	677.75	588.75	482.5	381.25	295.25	238.5	141.25	80.25	0	0	0
5		998	977	976.75	994.5	990.75	987.75	956	967.25	950.25	962	894	829	751	677.75	588.75	482.5	381.25	295.25	238.5	141.25	80.25	0	0	0
Sheet23

Cell Formulas
Range	Formula
B4:Y4	B4	=SUMIFS($B$2:$ZZ$2,$B$1:$ZZ$1,">="&B1,$B$1:$ZZ$1,"<="&EOMONTH(B1,11))
B5:Y5	B5	=SUM(B2:M2)

The B4 formula will sum up anything between the B1 date and the next 11 months. But if your columns are sequential months, like you showed, then you can use the simpler formula in B5.