Variable row number in Sum($A$1:A10) formula
 

I need to be able to change the length of the string of numbers in a column
being added using the Sum formula such as: =Sum($A$1:A10), where A10 is a
variable adjusted by a reference to the number of rows to add in an different
cell (C1).
For instance, if C1 is the reference cell and C1 = 20, the the fomula should
automatically change to =Sum($A$1:A20).
How can this be accomplished?

Variable row number in Sum($A$1:A10) formula
 

=SUM(INDIRECT("A1:A"&C1))
--
Gary''s Student - gsnu200909

Variable row number in Sum($A$1:A10) formula
 

=SUM(OFFSET(A1,0,0,C1,1))

Entered in B1


Gord Dibben MS Excel MVP



On Thu, 3 Dec 2009 11:45:01 -0800, Migo1
wrote:

I need to be able to change the length of the string of numbers in a column
being added using the Sum formula such as: =Sum($A$1:A10), where A10 is a
variable adjusted by a reference to the number of rows to add in an different
cell (C1).
For instance, if C1 is the reference cell and C1 = 20, the the fomula should
automatically change to =Sum($A$1:A20).
How can this be accomplished?

Variable row number in Sum($A$1:A10) formula
 

Another one...

=SUM(A1:INDEX(A:A,C1))

If C1 is an empty cell the entire range will be calculated.

--
Biff
Microsoft Excel MVP


"Migo1" wrote in message
...
I need to be able to change the length of the string of numbers in a column
being added using the Sum formula such as: =Sum($A$1:A10), where A10 is a
variable adjusted by a reference to the number of rows to add in an
different
cell (C1).
For instance, if C1 is the reference cell and C1 = 20, the the fomula
should
automatically change to =Sum($A$1:A20).
How can this be accomplished?