Dynamically sum a column

[puuts]

Hello,

I have a data from Column G to AV.
I would like to dynamically SUM column G based on the total rows count.
I want the result to be display in the last available row of Column G.

for example if the total count of column g is from G2:G99, i want the result to be display in G100 using the below formula:

Please Login or Register  to view this content.

Thanks in advance gurus!

[TMS]

Select Cell A1 and use the Name Manager to create a Named Range: ptrFirstCell =

Please Login or Register  to view this content.

Now, select A2 and use the Name Manager to create a Named Range: ptrPrevCell =

Please Login or Register  to view this content.

You can now use the formula in any cell, except row 1 (in any column) and it will sum all the cells from row 1 to the row before the formula:

Please Login or Register  to view this content.

If you put the formula in row 1 it will generate a circular reference.

Courtesy the book: Progessional Excel Development by Buulen, Bovey and Green

You can insert rows up to, and including the cell with the formula

Regards, TMS

[puuts]

Hi TMS,

thanks for reply. Is it possible to convert it to vba? This is just a part of I want to automate.

[JOHN H. DAVIS]

Maybe:

Please Login or Register  to view this content.

[puuts]

It works John!

how can I modify it if I want it until last row of column AV?

[JOHN H. DAVIS]

Maybe:

Please Login or Register  to view this content.

[puuts]

Maybe:

Please Login or Register  to view this content.

Hi John,

When I try the above code, it total only Column G.

[JOHN H. DAVIS]

My mistake, I thought you only wanted the last row value of AV. Try

Please Login or Register  to view this content.

[JOHN H. DAVIS]

Or are you trying to plant the formula accross from G to AV? Do all the columns hold numeric values?

[puuts]

Or are you trying to plant the formula accross from G to AV? Do all the columns hold numeric values?

That's right john. Across G to AV. Yes, they all hold numeric values.

[TMS]

Maybe this, then:

Please Login or Register  to view this content.

Regards, TMS

[JOHN H. DAVIS]

Maybe:

Please Login or Register  to view this content.

[TMS]

Hi John: would you not expect to put all the column totals on the same row?

[JOHN H. DAVIS]

I would, but I don't know the layout. If the columns are the same size, then they will be on the same row.