How can I add down a list a specified number of cells?

[porbeagle]

I have a huge number of lists that I am calculating. I have a vertical list
of numbers
each vertical cell represents a week. I am trying to add up a varing number
of week forward to madel stock controls. Currently I am adding up each
individual cell for the number of week that i need (ie 4 weeks is
=sum(a2+a3+a4+a5). This is very clumsy and time consuming. Is there a way to
write a formula that adds up a specified number of cells forward. i.e add ten
cells down the list and return total?

 

[Pete_UK]

Imagine you put the number of weeks you want to add in cell F1 (eg
10). Then you could use this formula:

=SUM(INDIRECT("A2:A"&F1+1)

Hope this helps.

Pete

 

[Ron Coderre]

Try something like this:

B1: (number of cells to add.....eg 10)
A1: =SUM(A2:INDEX(A:A,B1))

In that example, the formula in A1 sums cells A2:A10

Is that something you can work with?
Post back if you have more questions.
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

 

[Stephen]

=SUM(OFFSET(A2,0,0,4,1))
will give you the 4 week total in your example below. If you were to put
this formula in (say) B2 and then copy it to B3, it would give you a four
week total starting from A3.

Of course you could put a cell address in instead of the number 4, for
example
=SUM(OFFSET(A2,0,0,G17,1))
Then it would total however many weeks were entered into G17.
Or you could put a formula in there, and so on...

 

[Ron Coderre]

Clarification:
The value in cell B1 actually
indicates the Row Number to add through.

To use it to indicate how many cells to add
use this formula:
A1: =SUM(A2:INDEX(A:A,ROW(A2)+B1-1))

--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

 

[Pete_UK]

Sorry, missed a bracket off:

=SUM(INDIRECT("A2:A"&F1+1))

Pete

 

[porbeagle]

Thanks!!!!

Both these formula's work (although I am yet to work out how). Although due
to my column text headers I have added +4 to the end of each formula (where
you had +1 Pete and after your B1 Pete) to start the count from a few rows
down. Thanks guys you have turned a whole afternoon's work into a few hours,
at the means alot on a Friday!!!

 

[Ron Coderre]

You're welcome!
Thanks for letting us know that we helped!

***********
Regards,
Ron

XL2003, WinXP

 

[Pete_UK]

That's good to hear, and thanks for the feedback. In my formula, if F1
contained 10, then what it is doing is:

SUM(INDIRECT("A2:A"&F1+1)), which becomes:

=SUM(INDIRECT("A2:A"&11)), or:

=SUM(INDIRECT("A2:A11")), or:

=SUM(A2:A11)

i.e. the reference to the last cell in the range is created by adding
1 onto the number in F1. This is fine if your numbers start in A2, but
if they started in A5, say, and you wanted 10 of them, then you would
want the range to be A5:A14, in which case you would have to add 4
onto F1 to get the last cell.

Hope this explains a bit further.

Pete

 

[David Biddulph]

Other contributors have given a variety of answers to your question, but
would you like to enlighten us by telling us what value you think the SUM()
function adds to your formula?
What does =sum(a2+a3+a4+a5) do for you that =(a2+a3+a4+a5) doesn't?
You may wish to look up the SUM() function in Excel help.