Sum over a variable number of cells

[SD]

I am building a financial model and would like to sum values over a variable
number of preceeding cells (the number of cells should be inputted by a
user). How can I implement this functionality?

 

[akphidelt]

You can use sum and offset. For instance say your data is in A1 on down. You
put your variable in B1 And your result comes in C1. In C1 you put the formula

=SUM(OFFSET(A1,0,0,B1))

B1 is the number of cells that it will offset to. So if you put 3 and will
sum the first 3 cells, etc, etc.

 

[Chip Pearson]

The following formula will sum the values begriming in cell D2 and
extending to the right for the number of columns specified in cell A1.

=SUM(OFFSET($D$2,0,0,1,A1))

So, for example, if A1 = 4, the formula will sum the range D2:G2.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[RagDyer]

To sum Row 2, starting at A2, and user enters the number of columns to total
into A1:

=SUM(A2:INDEX(2:2,A1))

If A1 is empty, entire row will total.

To sum Column B, starting at B1, and user enters the number of rows to total
into A1:

=SUM(B1:INDEX(B:B,A1))
Same holds true if A1 is empty.

To allow user to define both start and end points, entering boundaries in
say A1 and A2 for Column B:

=SUM(INDEX(B:B,A1):INDEX(B:B,A2))