sum a dynamic range

[trakked]

Hi,

i am trying to get the last number in a column and get the sum result of that number up to 10 rows before.

so far i have this:
=INDIRECT(ADDRESS( MATCH( 9.99E306, C:C,1),3,4) )
which correctly gives me the value of the last row to contain data in a column

now what i am trying to do is to get the sum of a range that would go from the cell id in the above statement up to 10 rows before.

i guess it would look something like this
=SUM(INDIRECT(ADDRESS( MATCH( 9.99E306, C:C,1),3,4):R-10C)
this does not work and i am not to familiar with relative cells coordinates.

thanks for any help

[daddylonglegs]

ADDRESS gives you a cell value as a text string and then INDIRECT converts it back to a cell reference, best to avoid both those functions and use either OFFSET or INDEX, e.g.

=SUM(OFFSET(C1,MATCH(9.99E+306,C:C)-1,0,-10))

[Teethless mama]

Try this non volatile formula:

=SUM(INDEX(C:C,MATCH(10^10,C:C)-9):INDEX(C:C,MATCH(10^10,C:C)))

[zbor]

If you don't have blank cells you could use this:

=SUMPRODUCT(A1:A1000, --(ROW(A1:A1000)>COUNT(A1:A1000)-10))

[trakked]

Thanks People... works very well!