# Rolling 12 month forecast - backwards

#### yorkam

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

### Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

#### Eric W

##### MrExcel MVP

Book1
ABCDEFGHIJKLMNOPQRSTU
1Date1/1/20202/1/20203/1/20204/1/20205/1/20206/1/20207/1/20208/1/20209/1/202010/1/202011/1/202012/1/20201/1/20212/1/20213/1/20214/1/20215/1/20216/1/20217/1/20218/1/2021
2Value123456789101112131415161718
3
4Last 12 months150
Sheet2
Cell Formulas
RangeFormula
B4B4=SUMIFS(B2:ZZ2,B1:ZZ1,">="&EOMONTH(TODAY(),-12),B1:ZZ1,"<="&EOMONTH(TODAY(),0))

#### Massoud

##### New Member

Book1
ABCDEFGHIJKLMNOPQRSTU
1Date1/1/20202/1/20203/1/20204/1/20205/1/20206/1/20207/1/20208/1/20209/1/202010/1/202011/1/202012/1/20201/1/20212/1/20213/1/20214/1/20215/1/20216/1/20217/1/20218/1/2021
2Value123456789101112131415161718
3
4Last 12 months150
Sheet2
Cell Formulas
RangeFormula
B4B4=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.
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

##### MrExcel MVP
Welcome to the forum!

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

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

#### Massoud

##### New Member

=SUMIFS(B2:ZZ2,B1:ZZ1,">="&EOMONTH(A1,-12),B1:ZZ1,"<="&EOMONTH(A1,0))
Thank you, Eric. It worked and I appreciate the help.

#### CindaH

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

##### MrExcel MVP
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
ABCDEFGHIJKLMNOPQRSTUVWXY
1Date1-Jan-201-Feb-201-Mar-201-Apr-201-May-201-Jun-201-Jul-201-Aug-201-Sep-201-Oct-201-Nov-201-Dec-201-Jan-211-Feb-211-Mar-211-Apr-211-May-211-Jun-211-Jul-211-Aug-211-Sep-211-Oct-211-Nov-211-Dec-21
2Units94.2589.2588.51058988.5867868.568657873.2589106.25101.258656.7597.256180.25
3
4Rolling 12 Months998977976.75994.5990.75987.75956967.25950.25962894829751677.75588.75482.5381.25295.25238.5141.2580.25000
5998977976.75994.5990.75987.75956967.25950.25962894829751677.75588.75482.5381.25295.25238.5141.2580.25000
Sheet23
Cell Formulas
RangeFormula
B4:Y4B4=SUMIFS(\$B\$2:\$ZZ\$2,\$B\$1:\$ZZ\$1,">="&B1,\$B\$1:\$ZZ\$1,"<="&EOMONTH(B1,11))
B5:Y5B5=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.

Replies
0
Views
199
Replies
15
Views
240
Replies
0
Views
223
Replies
0
Views
346
Replies
4
Views
99

1,141,204
Messages
5,704,954
Members
421,372
Latest member
Jamie11

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