Sum last 10 numbers...
 

I have numbers in cells J2:J8 right now. I will be adding a number to cell
J9 tomorrow and J10 the next day and so on. I need a formula to sum the last
10 numbers in the J column. Thanks. I thought this would work but it is not
for some reason

=SUM(INDIRECT("J"&MAX(1,COUNTA(J:J)-10)&".J"&COUNTA(J:J)))

Any ideas? Thanks

One way:

=SUM(OFFSET(J2,MAX(0,COUNTA(J2:J65536)-10),0,10,1))

In article ,
Jambruins wrote:

I have numbers in cells J2:J8 right now. I will be adding a number to cell
J9 tomorrow and J10 the next day and so on. I need a formula to sum the last
10 numbers in the J column. Thanks. I thought this would work but it is not
for some reason

=SUM(INDIRECT("J"&MAX(1,COUNTA(J:J)-10)&".J"&COUNTA(J:J)))

Any ideas? Thanks

=SUM(OFFSET(INDIRECT("J"&MAX(MATCH(9.9999999999999 9E+307,J:J))),-9,0,10,1))

This will not work if you have fewer than 10 numbers in the column



"Jambruins" wrote:

I have numbers in cells J2:J8 right now. I will be adding a number to cell
J9 tomorrow and J10 the next day and so on. I need a formula to sum the last
10 numbers in the J column. Thanks. I thought this would work but it is not
for some reason

=SUM(INDIRECT("J"&MAX(1,COUNTA(J:J)-10)&".J"&COUNTA(J:J)))

Any ideas? Thanks

I like your approach better than mine


"JE McGimpsey" wrote:

One way:

=SUM(OFFSET(J2,MAX(0,COUNTA(J2:J65536)-10),0,10,1))

In article ,
Jambruins wrote:

I have numbers in cells J2:J8 right now. I will be adding a number to cell
J9 tomorrow and J10 the next day and so on. I need a formula to sum the last
10 numbers in the J column. Thanks. I thought this would work but it is not
for some reason

=SUM(INDIRECT("J"&MAX(1,COUNTA(J:J)-10)&".J"&COUNTA(J:J)))

Any ideas? Thanks