# Dynamic Sum

#### Cullen

##### New Member
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?

### Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
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
ABCD
11
22
33
44
55
66
77
88
99
101
1140
Sheet1

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
ABCD
11
22
33
44
55
66
77
88
99
101
1140
Sheet1

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?

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.

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

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

Replies
3
Views
132
Replies
10
Views
152
Replies
2
Views
278
Replies
8
Views
123
Replies
1
Views
57

1,203,094
Messages
6,053,507
Members
444,667
Latest member
KWR21

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back