Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports



Results 1 to 4 of 4

Thread: Sum Range Defined By Reference Cell

  1. #1
    Join Date
    1st August 2008
    Posts
    1

    Sum Range Defined By Reference Cell

    I have a list of numbers in Column A and in Column B I have a standard Sum formula which sums the adjacent number in Column A and the four numbers before it.

    A B

    1 =Sum(A1:A5)
    3 =Sum(A2:A6)
    5 ...
    3 ...
    5
    6

    I would like to have a formula which references another cell to define how many rows to sum from the starting cell. So in the above example, the total number of rows which are added together is 5. If I wanted to change all of the SUM formulas to add 6 rows, I would like to be able to change the number in the reference cell to 6 and all of the sum formulas would switch to adding 6 rows.

    Any advice greatfully received.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,784

    Re: Rows In Sum Range Defined By Reference Cell


    Try this formula. Array formulas will have curly brackets {} emcompassing the ENTIRE formula. You do not add these, you Enter the formula with Ctrl+Shift+Enter and Excel will add them (details: Array formulas). Many more Excel Formulas
     
     =SUM(INDIRECT("A1:A" & Cell-Ref)) 


  3. #3
    Join Date
    18th November 2004
    Location
    God's Own Country
    Posts
    4,648

    Re: Rows In Sum Range Defined By Reference Cell

    Hi,

    =SUM(A1:INDEX(A1:$A$32,$C$1))

    where C1 houses the reference.

    HTH

  4. #4
    Join Date
    23rd April 2008
    Location
    Work in North Wales
    Posts
    262

    Re: Sum Range Defined By Reference Cell

    If the first row you want to sum is column E and the last row is in F, try this in C1:

    =SUM(INDIRECT("A"&$E1&":A"&$F1)) and copy down.

    OR, if your sum always starts from the current row and just moves down x cells then put your x number in E1 and try this in C1:

    =sum(offset(A1,0,0,$E$1,1)) and copy down.

    Re-reading your post, I think the latter is what you want.

    edit: and now I see Krishnakumar has given you an even better non-volatile version! :-)

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Replies: 2
    Last Post: April 5th, 2008, 11:34
  2. Name Last Cell in Defined Range
    By robnot in forum Excel General
    Replies: 4
    Last Post: September 12th, 2007, 14:48
  3. Cells: Range Application-Defined Or Object-Defined Error
    By GuyGadois in forum Excel General
    Replies: 4
    Last Post: December 22nd, 2006, 17:30
  4. Replies: 2
    Last Post: August 17th, 2004, 09:54
  5. Setting cell values (without a defined range)
    By kotm14 in forum Excel General
    Replies: 5
    Last Post: February 24th, 2004, 07:44

Bookmarks

Posting Permissions

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