# Totaling Rows up to Row N

In excel 2010, Let's say that columns a+b+c+d+e+f+g=h

how do I total each row, (h), all the way to row N....?

The number of rows, (N), will keep increasing each week.
###### Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:
Let's say that you're in row 2 (allowing for headings in row 1).
The formula in H2 would be sum(b2:g2)
You can copy and paste that formula, but it's easier to "drag" it, or autofill it.
Click on H2, and you'll see a small dot in the bottom right corner of that cell. Point to the dot, then drag downwards. The formula is copied, and it always gives you the total for that row.
0
Commented:
If there isn't any other data in the H column below row N, you can do it like this:

=SUM(H:H)      <-- sums all numbers in the entire H column

But if you have a specific range you need to sum (e.g. sum of H1:HN) where "N" is some number, really you want to be able to construct the named range string dynamically.

E.g. Today you want the cell's value to be:

=SUM(H1:H55)

But next week, when there's more rows, you want it to be

=SUM(H1:H234)

The simplest way to accomplish this is just to manually edit the cell which is holding this formula.

But if you're trying to make it more automatic than that, you can use the INDIRECT() and CONCATENATE() functions to dynamically assemble a string that contains the named range you want to sum.

Example:

* You want to sum all of the cells from H1 through H234
* You have stored the value 234 in the cell "J1"

You can use the following formula to accomplish that:

=SUM(INDIRECT(CONCATENATE("H1",":","H",J1)))

The CONCATENATE() function will assemble the parts into a single string, e.g. "H3", ":", "H", and the value stored in the J1 cell are concatenated together, resulting in the string "H1:H234".

The INDIRECT() function takes that string and parses it into a named range

The SUM() function sums that named range.
0
Commented:
Here is a try: You may change column L by the lowest column in your sheet.

Have a macro to do the job. Can be assigned to a command button.
Here is an example in a worksheet.
L          M          N
1    vl      vm   Total
2    1      2      3
3    2      3      5
4    2      3      5
5

``````Private Sub UpdateN_Click()
Dim ws1 As Worksheet '
Dim aws1 As Worksheet ' active worksheet

Dim rno As Long 'row number
'Dim cno As Long ' column number
Dim rno1 As Long 'row number 1
Dim cno1 As Long ' column number 1
Dim rno2 As Long 'row number 2
Dim cno2 As Long ' column number 2
Dim cnt As Long 'count
Dim rng1 As Range
'
Set wb1 = Application.ActiveWorkbook
Set ws1 = Application.ActiveSheet
ws1.Activate
Set aws1 = wb1.ActiveSheet

'find start and end of rows in data
rno1 = aws1.Range("M1").End(xlUp).Row
rno2 = aws1.Range("M1").End(xlDown).Row
' assign column number for N column
cno1 = Cells(rno1, "N").Column
' copy N2 formula to new N cells
Dim N2Formula As String
Dim N2Range As Range
M2Formula = Cells(rno1 + 1, cno1).Formula
Set N2Range = aws1.Range(Cells(rno1 + 1, cno1), Cells(rno2, cno1))
N2Range.Formula = "=Sum(L" & rno1 + 1 & ":M" & rno1 + 1 & ")"
Cells(rno2 + 1, "N").Formula = "=Sum(N" & rno1 + 1 & ":N" & rno2 & ")"

End Sub
``````
Result:
L          M          N
1    vl      vm   Total
2    1      2      3
3    2      3      5
4    2      3      5
5            13

Result:
L          M          N
1    vl      vm   Total
2    1      2      3
3    2      3      5
4    2      3      5
5    9         9              13

Result:
L          M          N
1    vl      vm   Total
2    1      2      3
3    2      3      5
4    2      3      5
5    9         9              18
6                                31
0
Author Commented:
I'm sorry..... I meant to have a solution in VBA....not in a macro or any other method.

I need the solution in VBA please!

Thank you.
0
Commented:
This is really confusing, not just for you.
My comment was in VBA.
A piece of code in VBA is a procedure which is referred to as macro. This can be assigned to a button. Or just added as a click event code for the button.
try this sample Sheet.

totalN-dynamic.xlsm

Code is here
``````Private Sub UpdateN_Click()
Dim ws1 As Worksheet '
Dim aws1 As Worksheet ' active worksheet
Dim rno1 As Long 'row number 1
Dim cno1 As Long ' column number 1
Dim rno2 As Long 'row number 2
Dim cno2 As Long ' column number 2
Dim N2Formula As String
Dim N2Range As Range

Set wb1 = Application.ActiveWorkbook
Set ws1 = Application.ActiveSheet
ws1.Activate
Set aws1 = wb1.ActiveSheet

'find start and end of rows in data
rno1 = aws1.Range("G1").End(xlUp).Row
rno2 = aws1.Range("G1").End(xlDown).Row
' assign column number for H column
cno1 = Cells(rno1, "H").Column
' copy H2 formula to new H cells
M2Formula = Cells(rno1 + 1, cno1).Formula
'set the formula to the range or rewrite it as shown bellow
Set N2Range = aws1.Range(Cells(rno1 + 1, cno1), Cells(rno2, cno1))
N2Range.Formula = "=Sum(A" & rno1 + 1 & ":G" & rno1 + 1 & ")"
Cells(rno2 + 1, cno1).Formula = "=Sum(H" & rno1 + 1 & ":H" & rno2 & ")"

End Sub
``````
0

Experts Exchange Solution brought to you by