Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    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.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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.

  3. #3
    2 Star Lounger
    Join Date
    Oct 2008
    Posts
    141
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [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. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    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. #5
    2 Star Lounger
    Join Date
    Oct 2008
    Posts
    141
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [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
  •