sum a column with a variable
 

i want to sum up a column say from a1 to a12 which i s not too difficult. the
problem is that i sometimes want to sum up a3 to a12 or a5 to a12 depending
on what number has been entered. is there a way to make the function
sum(a1:a12) add different number of rows. is there any other way to do that.
could the number 1 in a1 be a variable somehow? abdul

sum a column with a variable
 

Answered in your other thread:
http://www.microsoft.com/communities...r=US&sloc=&p=1
--
"Actually, I *am* a rocket scientist." -- JB

Your feedback is appreciated, click YES if this post helped you.


"abdul" wrote:

i want to sum up a column say from a1 to a12 which i s not too difficult. the
problem is that i sometimes want to sum up a3 to a12 or a5 to a12 depending
on what number has been entered. is there a way to make the function
sum(a1:a12) add different number of rows. is there any other way to do that.
could the number 1 in a1 be a variable somehow? abdul

sum a column with a variable
 

Use INDIRECT():

We will put the limits in cells B1 and B2. In B1 enter:
3
and in B2 enter:
7

In B3 we can enter:

=SUM(INDIRECT("A" & B1 & ":A" & B2))
which basically pulls the limits out of B1 and B2, making it equivalent to:

=SUM(A3:A7)

So you can change the limits to the sum by changed in values in B1 & B2.
--
Gary''s Student - gsnu200832


"abdul" wrote:

i want to sum up a column say from a1 to a12 which i s not too difficult. the
problem is that i sometimes want to sum up a3 to a12 or a5 to a12 depending
on what number has been entered. is there a way to make the function
sum(a1:a12) add different number of rows. is there any other way to do that.
could the number 1 in a1 be a variable somehow? abdul

sum a column with a variable
 

Type a number from 1 to 12 in cell B1
In any other cell use =SUM(INDIRECT("A"&B1&":A12"))
If B1 holds the number 4 for example, then this formula is equivalent to
=SUM(A4:A12)
Is this what was required?
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"abdul" wrote in message
...
i want to sum up a column say from a1 to a12 which i s not too difficult.
the
problem is that i sometimes want to sum up a3 to a12 or a5 to a12
depending
on what number has been entered. is there a way to make the function
sum(a1:a12) add different number of rows. is there any other way to do
that.
could the number 1 in a1 be a variable somehow? abdul

sum a column with a variable
 

whatRow = InputBox("What row to start summing")
With Worksheets("Sheet1")
If IsEmpty(.Cells(.Rows.Count, 1)) Then
With .Cells(.Rows.Count, 1).End(xlUp)
.Offset(2, 0).Formula = "=Sum($A$" & whatRow & ":" & _
.Address & ")"
End With
End If
End With

"abdul" wrote:

i want to sum up a column say from a1 to a12 which i s not too difficult. the
problem is that i sometimes want to sum up a3 to a12 or a5 to a12 depending
on what number has been entered. is there a way to make the function
sum(a1:a12) add different number of rows. is there any other way to do that.
could the number 1 in a1 be a variable somehow? abdul

sum a column with a variable
 

whatRow = InputBox("What row to start summing")
With Worksheets("Sheet1")
If IsEmpty(.Cells(.Rows.Count, 1)) Then
With .Cells(.Rows.Count, 1).End(xlUp)
.Offset(2, 0).Formula = "=Sum($A$" & whatRow & ":" & _
.Address & ")"
End With
End If
End With

"abdul" wrote:

i want to sum up a column say from a1 to a12 which i s not too difficult. the
problem is that i sometimes want to sum up a3 to a12 or a5 to a12 depending
on what number has been entered. is there a way to make the function
sum(a1:a12) add different number of rows. is there any other way to do that.
could the number 1 in a1 be a variable somehow? abdul

sum a column with a variable
 

answered in your other posting
=SUM(INDEX(A:A,C1):INDEX(A:A,C2))
with C1 holding start row and C2 holding end row
--
Regards
Roger Govier

"abdul" wrote in message
...
i want to sum up a column say from a1 to a12 which i s not too difficult.
the
problem is that i sometimes want to sum up a3 to a12 or a5 to a12
depending
on what number has been entered. is there a way to make the function
sum(a1:a12) add different number of rows. is there any other way to do
that.
could the number 1 in a1 be a variable somehow? abdul