| View previous topic :: View next topic |
| Author |
Message |
brasse
Newbie
Joined: 06 Jan 2005
Posts: 1
|
 Posted: Thu Jan 06, 2005 10:03 am Post subject: Sum of dynamic range |
 |
|
Hello!
I am a bit of a spreadsheet newbie, but here goes.
If I want to calculate the sum of a dynamic number of cells, how would I do that?
In cell AX I want to calculate the sum of cells in the range A2 to A(X - 2). Does this make any sense? Can it be done?
:.:: brasse |
|
| Back to top |
|
 |
dfrench
Moderator
Joined: 03 Mar 2003
Posts: 1605
Location: Wellington, New Zealand
|
| Posted: Thu Jan 06, 2005 12:55 pm Post subject: |
|
|
You can calculate the range; express it as a string ( text) and use the indirect function
=SUM(INDIRECT("A2:A"&X-2)) where X is a named cell (or value) greater than 3 |
|
| Back to top |
|
|
jimlenn
General User
Joined: 01 Apr 2003
Posts: 39
Location: The cold, windswept, and great, upper Great Plains, USA.
|
| Posted: Fri Jan 07, 2005 8:48 am Post subject: |
|
|
| dfrench wrote: | You can calculate the range; express it as a string ( text) and use the indirect function
=SUM(INDIRECT("A2:A"&X-2)) where X is a named cell (or value) greater than 3 |
Can't you also define a dynamic range using the OFFSET function and then simply sum the range?
In the range definition use something like the following:
OFFSET($B$6;0;0;COUNT($B$6:$B$1000);1)
Then simply use SUM(Name)
This would sum range of any length starting in cell B6 and extending to B1000. |
|
| Back to top |
|
|
|
