# sum up

#### jillzz

##### New Member
Ok I'm really new at this and trying to learn on the fly. I know my questions are very basic. If i want to sum a column where I know the range will be ever changing, what is the most efficient way to write the code.

Thanks for you help in advance.

If there is a newbie area let me know and I'll go there. Or a basic tutorial to help me walk through simple formulas and such.

Jill

### Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use \$ signs: \$V\$2:\$Z\$99 will always point to V2:Z99, even after copying
Usually something like

=Subtotal(9,A1:a100) will allow you to add lines and also works when filtering.

otherwise = Sum(A1:A100).

jillzz said:
Ok I'm really new at this and trying to learn on the fly. I know my questions are very basic. If i want to sum a column where I know the range will be ever changing, what is the most efficient way to write the code.

Thanks for you help in advance.

If there is a newbie area let me know and I'll go there. Or a basic tutorial to help me walk through simple formulas and such.

Jill

=SUM(A:A)

or

=SUM(A2:A65536)

But that gives me a circular reference in the same column.

jillzz said:
But that gives me a circular reference in the same column.

Put the formula outside column A.

I can't put the formula in another column.
It needs to sum up in the same column or my spreadsheet will be all out of whack. Since other things pull from here.
Is there not another alternative?

jillzz said:
I can't put the formula in another column.
It needs to sum up in the same column or my spreadsheet will be all out of whack. Since other things pull from here.
Is there not another alternative?

In which cell are you trying to put the formula?

Put this after the last occupied cell in column A and change the A100 to whatever you last occupied cell reference is.

=Subtotal(9,A1:a100)

or =Sum(A1:A100)

The range changes each time I run another worksheet.
I
was trying to avoid having to actually go to the cell and enter a formula of =sum(A1:A24), since next week it might be =sum(A1:a52).

Isn't there a way to name the range based on the cells occupied and then tell it to sum the named range?

sorry, maybe I just don't understand what you are trying to tell me

Did you try the Subtotal formula.

I think that does what you want

=Subtotal(9,A1:a100), If you insert a line above the subtotal cell, it will take that into account when summing.

Replies
0
Views
192
Replies
1
Views
103
Replies
16
Views
252
Replies
2
Views
154
Replies
1
Views
119

1,203,462
Messages
6,055,563
Members
444,799
Latest member
CraigCrowhurst

### 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