Sum the last 10 entries in a dynamic range

[rozak]

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

 

[Matt Rogers]

Hi.

The last 5 rows only !!!

Excel Workbook
	C	D	E
1	1	*	500
2	1	*	*
3	1	*	*
4	1	*	*
5	1	*	*
6	1	*	*
7	1	*	*
8	100	*	*
9	100	*	*
10	100	*	*
11	100	*	*
12	100	*	*
13	*	*	*
Sheet

 

[rozak]

Great. Thanks Matt

 

[Matt Rogers]

No problem.
You are welcome!

 

[tusharm]

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

=SUM(OFFSET(M1,COUNT(M:M)-1-(10-1),0,10))

An alternative, if one prefers INDEX to OFFSET is

=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]

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]

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

 

[T. Valko]

You're welcome. Thanks for the feedback!