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"&X2)) 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"&X2)) 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 


