Announcement

Collapse
No announcement yet.

Unconfigured Ad Widget

Collapse

Sum Range Defined By Reference Cell

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

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

  • #2
    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)) 

    Comment


    • #3
      Re: Rows In Sum Range Defined By Reference Cell

      Hi,

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

      where C1 houses the reference.

      HTH
      Kris

      ExcelFox

      Comment


      • #4
        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! :-)

        Comment

        Trending

        Collapse

        There are no results that meet this criteria.

        Working...
        X