Results 1 to 5 of 5

20090417, 14:19 #1
 Join Date
 Oct 2008
 Posts
 141
 Thanks
 0
 Thanked 0 Times in 0 Posts
Hi,
I'm looking for a quick formula that will sum the last 10 values in a column. What makes it complicated is that the number of rows containing the values can vary from column to column. So basically, I need to find the last empty cell (row) in a column and offset it by 10 to get a range, and then I can use the sum function.
Cheers.

20090417, 14:28 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
For column A, you can use the following formula:
=SUM(OFFSET(A$1,MATCH(9.99999999999999E+307,A:A)10,0,10,1))
For column F, change the references from A to F.
If you need to sum the last 15 cells instead of the last 10, change both occurrences of 10 to 15.
Note: 9.99999999999999E+307 is the largest number you can enter in a cell.

20090417, 14:40 #3
 Join Date
 Oct 2008
 Posts
 141
 Thanks
 0
 Thanked 0 Times in 0 Posts
[quote name='HansV' post='771079' date='17Apr2009 15:28']For column A, you can use the following formula:
=SUM(OFFSET(A$1,MATCH(9.99999999999999E+307,A:A)10,0,10,1))
For column F, change the references from A to F.
If you need to sum the last 15 cells instead of the last 10, change both occurrences of 10 to 15.
Note: 9.99999999999999E+307 is the largest number you can enter in a cell.[/quote]
Great. Thanks. I found that with this formula, it actually sums the last 9 numbers rather than 10, so I changed the '10' to '11'.

20090417, 14:49 #4
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
Perhaps you have nonnumeric values in between the numbers? The formula I posted DOES sum the last 10 cells starting from the last nonblank cell upwards.

20090417, 14:57 #5
 Join Date
 Oct 2008
 Posts
 141
 Thanks
 0
 Thanked 0 Times in 0 Posts
[quote name='HansV' post='771090' date='17Apr2009 15:49']Perhaps you have nonnumeric values in between the numbers? The formula I posted DOES sum the last 10 cells starting from the last nonblank cell upwards.[/quote]
You are correct. The mistake that I've made was to insert a new row after I assigned the formula. That made A1 go to B1, and so it summed an empty cell.