# Thread: summ column ... (2000)

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

2. ## Re: summ column ... (2000)

Why don't you want to use formulas?

3. ## Re: summ column ... (2000)

if is possible with macro because the table not is fixed but dinamic...

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

5. ## Re: summ column ... (2000)

A formula can also be dynamic....

Steve

6. ## Re: summ column ... (2000)

If you put the array formula in A1 (confirm with ctrl-shift-enter):
=MAX(IF(A5:A65536<>"",ROW(A5:A65536)))

Then In B3:
=SUM(OFFSET(B5,0,0,\$A\$1-4,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

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

8. ## Re: summ column ... (2000)

special tks to Hans, great tks to Steve for suggestion.

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

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

11. ## 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)...

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

13. ## Re: summ column ... (2000)

WIZARD....!!!!
Tks.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•