Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2003
    Posts
    311

    Unanswered: I want to sum from 1 row above current cell to cell 7 ?

    I want to sum from 1 row above current cell to cell 7

    say i am in col b row 55
    i want my formula to be "=Sum(b54:b7)"

    i have used things like "=sum(b" & cntr & ":b7)"
    where i have counted rows, but this seems rediculous (worse then my spelling!)

    can i get the row # of the active cell or something like that?


    M~

  2. #2
    Join Date
    Jan 2002
    Location
    Bay Area
    Posts
    511
    This Excel solution may help. A function can be used to sum rows above the current cell. One thing I don't understand is that you have to re-enter the formula if you change values in the cells above.

    In the cell where you want the total, type "=sumtorow7()", without quotes. The function skips over cells that are empty. I have attached an Excel file to show this function working.

    Can anyone make this solution perfect, or is it just a fact of life that you need to refresh a function formula each time you change the data?

    Function SumToRow7()
    'sums only cells above that have values, up to row 7

    Dim i, BottomRow, TopRow As Integer

    TopRow = 7
    BottomRow = ActiveCell.Row - 1

    For i = TopRow To BottomRow
    If Trim(Cells(i, ActiveCell.Column).Value) <> "" Then
    total = total + Cells(i, ActiveCell.Column).Value
    End If
    Next

    SumToRow7 = total

    End Function
    Attached Files Attached Files

  3. #3
    Join Date
    Oct 2003
    Posts
    1,091
    Try this:

    Code:
    Sub MakeTotalRows()
        
        Dim myTotals As Range
        Dim myRange As Range
        Dim myRows As Integer
        Dim myColumns As Integer
        Dim i As Integer
        
    '   Set range
        Set myRange = ActiveCell.CurrentRegion
        
    '   Count number of rows & columns
        myRows = myRange.Rows.Count
        myColumns = myRange.Columns.Count
        
    '   Start at top, go to first column in last row
        myRange.Range("A1").Select
        ActiveCell.Offset(myRows - 1, 0).Select
        
        Application.ScreenUpdating = False
    
        
    '   Cycle through columns, entering Sum, Total, coloring
    '   Checks each column for numeric value, if so, places
    '   SUM function of that column in the cell;
    '   If not then places word "Total" in cell.
        For i = 0 To myColumns - 1
            If IsNumeric(ActiveCell.Offset(0, i).Value) Then
                ActiveCell.Offset(1, i).FormulaR1C1 = "=SUM(R[-" & myRows - 1 & "]C:R[-1]C)"
                ActiveCell.Offset(1, i).Font.Bold = True
                ActiveCell.Offset(1, i).Font.ColorIndex = 55
                ActiveCell.Offset(1, i).Borders.Weight = xlMedium
                ActiveCell.Offset(1, i).Borders.ColorIndex = 55
                ActiveCell.Offset(1, i).Interior.ColorIndex = 36
    
            Else
                ActiveCell.Offset(1, i).Value = "Total"
                ActiveCell.Offset(1, i).Font.ColorIndex = 55
                ActiveCell.Offset(1, i).Font.Bold = True
                ActiveCell.Offset(1, i).Borders.Weight = xlMedium
                ActiveCell.Offset(1, i).Borders.ColorIndex = 55
                ActiveCell.Offset(1, i).Interior.ColorIndex = 36
    
            End If
        Next i
        
        Application.ScreenUpdating = True
    
    End Sub
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

Posting Permissions

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