Dynamically sum a column

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:

=IF(SUM(G2:TOTAL COUNT OF ROW OF COLUMN G)=0,"",SUM(G2:TOTAL COUNT OF ROW OF COLUMN G))

Thanks in advance gurus!

Hi TMS,

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

Maybe:

Sub puutsy()
Range("G" & Range("G" & Rows.Count).End(3)(2).Row).Formula = "=IF(SUM(" & Range("G2:G" & Range("G" & Rows.Count).End(3).Row).Address(False, False) & ")=0,"""",SUM(" & Range("G2:G" & Range("G" & Rows.Count).End(3).Row).Address(False, False) & "))"
End Sub

It works John!

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

Maybe:

Range("G" & Range("G" & Rows.Count).End(3)(2).Row).Formula = "=IF(SUM(" & Range("G2:G" & Range("AV" & Rows.Count).End(3).Row).Address(False, False) & ")=0,"""",SUM(" & Range("G2:G" & Range("AV" & Rows.Count).End(3).Row).Address(False, False) & "))"
End Sub

Originally Posted by JOHN H. DAVIS

Maybe:

Range("G" & Range("G" & Rows.Count).End(3)(2).Row).Formula = "=IF(SUM(" & Range("G2:G" & Range("AV" & Rows.Count).End(3).Row).Address(False, False) & ")=0,"""",SUM(" & Range("G2:G" & Range("AV" & Rows.Count).End(3).Row).Address(False, False) & "))"
End Sub

Hi John,

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

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

Sub puutsx()
Range("AV" & Range("AV" & Rows.Count).End(3)(2).Row).Formula = "=IF(SUM(" & Range("AV2:AV" & Range("AV" & Rows.Count).End(3).Row).Address(False, False) & ")=0,"""",SUM(" & Range("AV2:AV" & Range("AV" & Rows.Count).End(3).Row).Address(False, False) & "))"
End Sub

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

Originally Posted by JOHN H. DAVIS

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.

Maybe:

Sub puutsw()
Dim i As Long, y As Long
With Application
    .ScreenUpdating = False
    .Calculation = xlCalculationManual
End With
For i = 7 To 48
y = Cells(Rows.Count, i).End(3).Row
    For Each numrange In Range(Cells(2, i), Cells(y, i)).SpecialCells(xlConstants, xlNumbers).Areas
        sumaddr = numrange.Address(False, False)
        numrange.Offset(numrange.Count, 0).Resize(1, 1).Formula = "=IF(SUM(" & sumaddr & ")=0,"""",SUM(" & sumaddr & "))"
        c = numrange.Count
    Next numrange
Next i
nodata:
With Application
    .ScreenUpdating = True
    .Calculation = xlCalculationAutomatic
End With
End Sub

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