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

mikezcg · #1 (**permalink**) 02-09-04, 17:53

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~

JerryDal · #2 (**permalink**) 02-09-04, 21:15

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

shades · #3 (**permalink**) 02-10-04, 09:25

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

Internet Services

Web Development

Digital Marketing

Consumer Tech