sum a column with a variable

[abdul]

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

 

[JBeaucaire]

Answered in your other thread
http://www.microsoft.com/communitie...19d-9ec7-899d6e6086cd&lang=en&cr=US&sloc=&p=1

 

[Gary''s Student]

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.

 

[Bernard Liengme]

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

 

[Mike]

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

 

[Mike]

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

 

[Roger Govier]

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