Results 1 to 13 of 13
Thread: summ column ... (2000)

20080104, 15:55 #1
 Join Date
 Jan 2004
 Location
 Italy
 Posts
 3,245
 Thanks
 0
 Thanked 0 Times in 0 Posts
summ column ... (2000)
How to summ value from column in sheet instead formula?
Example:
summing from B5 to B69 put total in B3...
summing from C5 to C69 put total in C3...
...
summing from N5 to N69 put total in N3...
the limit of each summ is the last filled cell in A and the last fille row in line 4

20080104, 16:01 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
Re: summ column ... (2000)
Why don't you want to use formulas?

20080104, 16:05 #3
 Join Date
 Jan 2004
 Location
 Italy
 Posts
 3,245
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: summ column ... (2000)
if is possible with macro because the table not is fixed but dinamic...

20080104, 16:12 #4
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: summ column ... (2000)
If you don't want a formula you can:
Use the sum formula then copy/paste the value into the cells in row 3.
OR
perhaps you could manually add them together and then type in the value in the cells.
I don't understand what is wrong with having a formula. Formulas are nice since they will update themselves when the data changes. if you do not have a formula, someone will have to manually edit the values in row 3 when the items change
To update automatically without a formula you could Use a macro to do it and enter the value on a change in the sheet.
Steve

20080104, 16:13 #5
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: summ column ... (2000)
A formula can also be dynamic....
Steve

20080104, 16:20 #6
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: summ column ... (2000)
If you put the array formula in A1 (confirm with ctrlshiftenter):
=MAX(IF(A5:A65536<>"",ROW(A5:A65536)))
Then In B3:
=SUM(OFFSET(B5,0,0,$A$14,1))
And copied B3 to C3:N3, the sum range would expand to include any new items added into column A....
Is this what you want: a formula that expands?
Steve

20080104, 16:21 #7
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
Re: summ column ... (2000)
Steve has given you several good suggestions, but here is a macro:
Sub MakeSum()
Dim lngMaxRow As Long
Dim lngMaxCol As Long
Dim lngCol As Long
lngMaxRow = Cells(Rows.Count, 1).End(xlUp).Row
lngMaxCol = Cells(4, Columns.Count).End(xlToLeft).Column
For lngCol = 2 To lngMaxCol
Cells(3, lngCol) = Application.WorksheetFunction.Sum( _
Range(Cells(5, lngCol), Cells(lngMaxRow, lngCol)))
Next lngCol
End Sub
It will have to be run each time the data change.

20080104, 16:32 #8
 Join Date
 Jan 2004
 Location
 Italy
 Posts
 3,245
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: summ column ... (2000)
special tks to Hans, great tks to Steve for suggestion.

20080104, 16:42 #9
 Join Date
 Jan 2004
 Location
 Italy
 Posts
 3,245
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: summ column ... (2000)
Hans is possible to use the same tecnique to add total in column N for each orizzontal range do until last cell in column A is filled?
Example:
summing range B5:M5 put total in cell in column N
summing range B6:M6 put total in cell in column N
...
summing range B69:M69 put total in cell in column N
In this case the column of total is N but the sheet is dinamic...

20080104, 16:56 #10
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
Re: summ column ... (2000)
The problem is that the code cannot know whether the last filled column contains data or totals, so if you run the code twice, you'll get two columns with totals.
If you want to use code, it would be better to place the column with totals to the left of the data instead of the the right, just like the other totals are above the data instead of below them.

20080104, 21:56 #11
 Join Date
 Jan 2004
 Location
 Italy
 Posts
 3,245
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: summ column ... (2000)
in each case the column to insert the total is where in line 4 is present the field named "TOT" (intercept this field name)...

20080104, 22:20 #12
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
Re: summ column ... (2000)
Try this version:
Sub MakeSum()
Dim lngMaxRow As Long
Dim lngMaxCol As Long
Dim lngRow As Long
Dim lngCol As Long
lngMaxRow = Cells(Rows.Count, 1).End(xlUp).Row
lngMaxCol = Range("4:4").Find(What:="TOT", LookIn:=xlValues, _
LookAt:=xlWhole).Column
' Row totals
For lngRow = 5 To lngMaxRow
Cells(lngRow, lngMaxCol) = Application.WorksheetFunction.Sum( _
Range(Cells(lngRow, 2), Cells(lngRow, lngMaxCol  1)))
Next lngRow
' Column totals
For lngCol = 2 To lngMaxCol
Cells(3, lngCol) = Application.WorksheetFunction.Sum( _
Range(Cells(5, lngCol), Cells(lngMaxRow, lngCol)))
Next lngCol
End Sub

20080104, 22:23 #13
 Join Date
 Jan 2004
 Location
 Italy
 Posts
 3,245
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: summ column ... (2000)
WIZARD....!!!!
Tks.