A Simple Sum?

Domigos

New Member
Joined
Mar 20, 2002
Messages
11
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

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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
Joined
Mar 2, 2002
Messages
1,934
Hi Domigos,

This also can do it:

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

Eli
 

Domigos

New Member
Joined
Mar 20, 2002
Messages
11
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
 

Forum statistics

Threads
1,144,509
Messages
5,724,770
Members
422,577
Latest member
kayth891

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top