# Thread: Sum of last 10 values in a column

1. 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.

2. 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.

3. [quote name='HansV' post='771079' date='17-Apr-2009 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'.

4. Perhaps you have non-numeric values in between the numbers? The formula I posted DOES sum the last 10 cells starting from the last non-blank cell upwards.

5. [quote name='HansV' post='771090' date='17-Apr-2009 15:49']Perhaps you have non-numeric values in between the numbers? The formula I posted DOES sum the last 10 cells starting from the last non-blank 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.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•