Sum the last 10 entries in a dynamic range

rozak

New Member
Syntax: Find the last entry in col C, then sum that plus the 9 entries above it.

=SUM(LOOKUP(2,1/(C:C<>""),C:C):OFFSET(LOOKUP(2,1/(C:C<>""),C:C),-9,,,))

Separately, the LOOKUP function works fine, but returns an error when used with OFFSET (in this case anyway).
Thanks

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

Matt Rogers

Well-known Member
Hi.

The last 5 rows only !!!
Excel Workbook
CDE
11*500
21**
31**
41**
51**
61**
71**
8100**
9100**
10100**
11100**
12100**
13***
Sheet

Last edited:

rozak

New Member
Great. Thanks Matt

No problem.
You are welcome!

tusharm

MrExcel MVP
The simplest formula, IMO, is below. It assumes the data are in column M starting with M1.

Code:
``=SUM(OFFSET(M1,COUNT(M:M)-1-(10-1),0,10))``
An alternative, if one prefers INDEX to OFFSET is
Code:
``=SUM(INDEX(M:M,COUNT(M:M)-(10-1)):INDEX(M:M,COUNT(M:M)))``
The 2 references to 10 are to the number of elements you want to include in the SUM.
Syntax: Find the last entry in col C, then sum that plus the 9 entries above it.

=SUM(LOOKUP(2,1/(C:C<>""),C:C):OFFSET(LOOKUP(2,1/(C:C<>""),C:C),-9,,,))

Separately, the LOOKUP function works fine, but returns an error when used with OFFSET (in this case anyway).
Thanks

T. Valko

Well-known Member
Here's another one.

Assumes there will always be at least 10 numbers to sum and the data is in a contiguous range.

=SUM(OFFSET(C1,MATCH(1E100,C:C)-1,0,-10))

rozak

New Member
How good is Excel! There's always another way. Spoiled for choices.
Thanks everyone.

T. Valko

Well-known Member
You're welcome. Thanks for the feedback!

Replies
1
Views
228
Replies
6
Views
1K
Replies
2
Views
291
Replies
3
Views
670
Replies
2
Views
90

1,190,646
Messages
5,982,113
Members
439,755
Latest member
nicos18

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.

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