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

1. Registered User
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. Registered User
Join Date
Jan 2002
Location
Bay Area
Posts
513
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

3. Registered User
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```

#### Posting Permissions

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