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.
dm2121Asked:
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.

stevepcguyCommented:
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
Frosty555Commented:
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
hnasrCommented:
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

Open in new window

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

New row added:
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
dm2121Author 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
hnasrCommented:
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

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Applications

From novice to tech pro — start learning today.