Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help with Offset | Excel Discussion (Misc queries) | |||
Compare Cell Values, Offset(-1,0), Offset(-1,-1), and xlFillDefaul | Excel Worksheet Functions | |||
Offset | Excel Worksheet Functions | |||
Using Offset in VBA | Excel Discussion (Misc queries) | |||
SUM(OFFSET)? | Excel Worksheet Functions |