Dynamic Sum

[Cullen]

I have a list that is populated from a userform. At the bottom I would like to add up the above data. It would reach from the bottom cell up to D14. I have tried assigning it as a range but it is not working. Any suggestions?

 

[venkat1926]

I have given you a trivial example data sheet.
I want to indicate the sum in A11 from A4 to end of the data i.e. a10
see the formula in A11
if you insert few rows anywhere within the data or end of the data and enter some data the sum will be shifted down and correct sum will be given


you modify this to suit you. see the help under "offset funcion " in help excel sheet.

Book3
	A	B	C	D
1	1
2	2
3	3
4	4
5	5
6	6
7	7
8	8
9	9
10	1
11	40
Sheet1

 

[venkat1926]

what is meant by "dynamic" you mean the the no. of rows in data range may expand or shrink by addition of rows or deletion of rows.

I have given you a trivial example data sheet.
I want to indicate the sum in A11 from A4 to end of the data i.e. a10
see the formula in A11
if you insert few rows anywhere within the data or end of the data and enter some data the sum will be shifted down and correct sum will be given


you modify this to suit you. see the help under "offset funcion " in help excel sheet.

Book3
	A	B	C	D
1	1
2	2
3	3
4	4
5	5
6	6
7	7
8	8
9	9
10	1
11	40
Sheet1

 

[Cullen]

Thanks for your help. The only problems I see is that I need a dynamic definition for the cells above the cell displaying the totals. =SUM(D14:OFFSET(D?,-1,0,1,1)). I can find the cell that I need the totals in easily:

ActiveSheet.Range("D14").End(xlDown).Offset(3, 0).Select
ActiveCell.FormulaR1C1 = "=Sum(X:X)"

How can I put something that will sum from D14 down to the cell with the formula. In other words, what code would be used to define a range from D14 to the cell directly above the cell with the formula regardless of the size?

 

[mikerickson]

That's the beauty of R1C1 notation

=SUM(R1C:R[-1]C) will sum from Row 1 to the row above the formula of the column in which the formula resides.

ActiveSheet.Range("D14").End(xlDown).Offset(3, 0).FormulaR1C1 = "=SUM(R14C,R[-3]C)"

 

[Cullen]

I was hoping someone would enlighten me as you have. That works perfect. Thank you so much, you've saved me a great deal of frustration