Results 1 to 3 of 3
  1. #1
    Lounger
    Join Date
    Apr 2008
    Posts
    41
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Sum from last row - 'x' (Excel 2003)

    I thought I could figure out a simple array formula to accomplish this, but find it harder than I thought. Conditionally, I want to sum rows in a column, starting with the last row and summing backwards a number of rows based on that condition. I have an attachment as an example. Is this possible without VBA?
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Sum from last row - 'x' (Excel 2003)

    You write "If count > 7 then sum last cell through (last cell - 7)" but your example formula uses "sum last cell through (last cell - 6)". Similar for if the count is 7 or less.

    Assuming that your example formulas indicate what you want, you could use
    <code>
    =SUM(IF(COUNT(A:A)>7,OFFSET(A1,COUNT(A:A)-6,0,7),OFFSET(A1,COUNT(A:A)-2,0,3)))
    </code>
    This is a 'normal' formula, not an array formula.
    It assumes that there are no blanks in the range from A1 to the last filled cell in column A.

  3. #3
    2 Star Lounger
    Join Date
    Aug 2004
    Posts
    123
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Sum from last row - 'x' (Excel 2003)

    IF there are blank cells inside the range of column A, you can consider to use :

    =SUM(INDEX(A:A,MATCH(9.9E+307,A:A)-IF(COUNT(A:A)>7,6,2)):INDEX(A:A,MATCH(9.9E+307,A:A )))

    Regards
    Bosco

Posting Permissions

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