Sum of last 10 values in a column

Topic

New Reply

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.

Reply | Quote

Replies

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.

Reply | Quote

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.

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

Reply | Quote

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.

Reply | Quote

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.

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.

Reply | Quote