# A Simple Sum?

#### Domigos

##### New Member
I have a spreadsheet with long columns of numbers, and I really want a way of having a single formula in a cell at the top of each column which sums the last six items in the column. Sounds simple doesn't it - but it is really doing my head in at the moment.

I'm sure that one of you clever guys out there has come across this before - any ideas that would point me in the right direction?

### Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

##### MrExcel MVP
On 2002-10-24 11:02, Domigos wrote:
I have a spreadsheet with long columns of numbers, and I really want a way of having a single formula in a cell at the top of each column which sums the last six items in the column. Sounds simple doesn't it - but it is really doing my head in at the moment.

I'm sure that one of you clever guys out there has come across this before - any ideas that would point me in the right direction?

(1.) Activate Insert|Name|Define.
(2.) Enter BigNum in the Names in Workbook box.
(3.) Enter the following in the Refers to box:

9.99999999999999E+307

(4.) Click OK.

Let A be the first column whose last 6 items you want to sum...

In A1 enter & copy across:

=SUM(OFFSET(A2,MATCH(BigNum,A:A)-CELL("Row",A2),0,-6,1))

#### eliW

##### Well-known Member
Hi Domigos,

This also can do it:

=SUM(OFFSET(A2:A65536,COUNT(A2:A65536)-6,0,6,1))

Eli

#### Domigos

##### New Member
Aladin, you really are the dogs doo daa's when it comes to this stuff - what can I say, your elegant solution works like a dream.....

Have now moved on a bit with the project and have added a second column that gives me a running total alongside the original column (using an IF Formula) - I now have the problem of trying to get your excellent formula to work when there is a whole series of zeros beneath the figures I am interested in! Have not succeeded yet - but I am still working away at it. It would appear this is not quite as easy as I thought it might be! (There's an understatement if ever there was one).

Many sincere thanks for your efforts on my behalf.

Domigos

Replies
3
Views
229
Replies
6
Views
110
Replies
1
Views
37
Replies
10
Views
370
Replies
11
Views
370

1,181,682
Messages
5,931,402
Members
436,788
Latest member
Oteez

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