SUM & OFFSET
 

I have a sheet set up similar to what is below and Im trying to return the
sum of the first 4 cells in column D which contain data and correspond to a
name in column A. Any blank cells to be skipped until it finds 4 cells to sum.
There can be numerous entries in column D which correspond to a name in
column A, the names will be different but the €œsection€� reference will always
be the same. I can get the formula =IF(A2="section",SUM(OFFSET(D2,1,,4,)),"")
to work for the first name, but I cant seem to get past the blank cells
which are under the second name in the example.

A1 NAME D1
A2 section D2
A3 D3 8
A4 D4 6.5
A5 D5 6
A6 D6 4.5
A7 NAME 2 D7
A8 section D8
A9 D9 7
A10 D10
A11 D11 8
A12 D12
A13 D13
A14 D14 7.5
A15 D15 5.5

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200902/1

SUM & OFFSET
 

I think you need a mcro. this macro will put the sums in column E

Sub createsums()

Lastrow = Range("D" & Rows.Count).End(xlUp).Row
StartSection = False
RowCount = 1
Do While RowCount <= Lastrow
If Range("A" & RowCount) = "section" Then
StartRow = RowCount
StartSection = True
Else
If StartSection = True Then
If Range("A" & (RowCount + 1)) < "" Or _
RowCount = Lastrow Then

Range("E" & RowCount).Formula = _
"=SUM(D" & StartRow & ":D" & RowCount & ")"
StartSection = False
End If
End If
End If
RowCount = RowCount + 1
Loop

End Sub

"KevinE via OfficeKB.com" wrote:

I have a sheet set up similar to what is below and Im trying to return the
sum of the first 4 cells in column D which contain data and correspond to a
name in column A. Any blank cells to be skipped until it finds 4 cells to sum.
There can be numerous entries in column D which correspond to a name in
column A, the names will be different but the €œsection€� reference will always
be the same. I can get the formula =IF(A2="section",SUM(OFFSET(D2,1,,4,)),"")
to work for the first name, but I cant seem to get past the blank cells
which are under the second name in the example.

A1 NAME D1
A2 section D2
A3 D3 8
A4 D4 6.5
A5 D5 6
A6 D6 4.5
A7 NAME 2 D7
A8 section D8
A9 D9 7
A10 D10
A11 D11 8
A12 D12
A13 D13
A14 D14 7.5
A15 D15 5.5

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200902/1