Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

# Thread: Sum Range Defined By Reference Cell

1. I agreed to these rules
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.

Excel Video Tutorials / Excel Dashboards Reports

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))

3. Super Moderator
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. ## 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.

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

Excel Video Tutorials / Excel Dashboards Reports

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

#### 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