# Sum last 12 or 24 values

#### SimonHughes

##### Active Member
Hello, I have a column of monthly sales in the range D2:D90 (next month it will be D2:D91 and so on). I want to sum and average the last six, 12, 18 months ect but cannot work out the formula. The first formula will need to sum cells D85:D90. Any help would be appreciated, many thanks

I am working on Excel 2010

### Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

#### ukmikeb

##### Well-known Member
Perhaps :-
SimonHughes
DEFGHI
856
865
874
883
892Last 6Last 12Last 18Last 24
9012178171300
913.506.509.5012.50

<tbody>
</tbody>
Excel 2007

Worksheet Formulas
CellFormula
F90=SUM(OFFSET(\$D\$1,COUNT(\$D:\$D),,-COLUMNS(\$F:F)*6,1))
G90=SUM(OFFSET(\$D\$1,COUNT(\$D:\$D),,-COLUMNS(\$F:G)*6,1))
H90=SUM(OFFSET(\$D\$1,COUNT(\$D:\$D),,-COLUMNS(\$F:H)*6,1))
I90=SUM(OFFSET(\$D\$1,COUNT(\$D:\$D),,-COLUMNS(\$F:I)*6,1))
F91=AVERAGE(OFFSET(\$D\$1,COUNT(\$D:\$D),,-COLUMNS(\$F:F)*6,1))
G91=AVERAGE(OFFSET(\$D\$1,COUNT(\$D:\$D),,-COLUMNS(\$F:G)*6,1))
H91=AVERAGE(OFFSET(\$D\$1,COUNT(\$D:\$D),,-COLUMNS(\$F:H)*6,1))
I91=AVERAGE(OFFSET(\$D\$1,COUNT(\$D:\$D),,-COLUMNS(\$F:I)*6,1))

<tbody>
</tbody>

<tbody>
</tbody>

SimonHughes
DEFGHI
856
865
874
883
892Last 6Last 12Last 18Last 24
9013182169292
91165.176.839.3912.17

<tbody>
</tbody>
Excel 2007

Worksheet Formulas
CellFormula
F90=SUM(OFFSET(\$D\$1,COUNT(\$D:\$D),,-COLUMNS(\$F:F)*6,1))
G90=SUM(OFFSET(\$D\$1,COUNT(\$D:\$D),,-COLUMNS(\$F:G)*6,1))
H90=SUM(OFFSET(\$D\$1,COUNT(\$D:\$D),,-COLUMNS(\$F:H)*6,1))
I90=SUM(OFFSET(\$D\$1,COUNT(\$D:\$D),,-COLUMNS(\$F:I)*6,1))
F91=AVERAGE(OFFSET(\$D\$1,COUNT(\$D:\$D),,-COLUMNS(\$F:F)*6,1))
G91=AVERAGE(OFFSET(\$D\$1,COUNT(\$D:\$D),,-COLUMNS(\$F:G)*6,1))
H91=AVERAGE(OFFSET(\$D\$1,COUNT(\$D:\$D),,-COLUMNS(\$F:H)*6,1))
I91=AVERAGE(OFFSET(\$D\$1,COUNT(\$D:\$D),,-COLUMNS(\$F:I)*6,1))

<tbody>
</tbody>

<tbody>
</tbody>

Data series 1 to 89 reversed in D2:D90 in first table.

hth

##### MrExcel MVP
Hello, I have a column of monthly sales in the range D2:D90 (next month it will be D2:D91 and so on). I want to sum and average the last six, 12, 18 months ect but cannot work out the formula. The first formula will need to sum cells D85:D90. Any help would be appreciated, many thanks

I am working on Excel 2010

Something like:

In F3 enter...

=SUM(OFFSET(INDEX(D:D,MATCH(9.99999999999999E+307,D:D)),0,0,-F2))

where F2 houses a value like 6 (12, 18, etc.).

#### SimonHughes

##### Active Member
Thanks Mike, the worksheet formula works well. I have just tweaked the formula to suit my worksheet layout but the offset works just fine, many thanks

#### SimonHughes

##### Active Member
Hi Aladin, yes, that also works perfectly. Is there any specific benefit in using one or the other formula? Many thanks.

##### MrExcel MVP
Hi Aladin, yes, that also works perfectly. Is there any specific benefit in using one or the other formula? Many thanks.

The formula with OFFSET and INDEX\MATCH evaluates from the last value up and won't be affected by the presence of in-between blanks.

#### SimonHughes

##### Active Member
Thanks for the explanation. In this scenario either will work as well as each other then.

Replies
10
Views
778
Replies
1
Views
367
Replies
3
Views
966
Replies
5
Views
1K
Replies
6
Views
2K

1,195,640
Messages
6,010,880
Members
441,571
Latest member
stolenweasel

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