Bernie Deitrick
3/26/2010 5:30:00 PM
And there are two non-formula methods that I should also mention:
Put 1,2,3, etc. in column A, then sort based on column A, and use Subtotals
based on changed values in column A.
Put 1,2,3, etc. in column A, then use a pivot table on all your data, using
Column A as the row data,and the data that you are summing as the data data.
HTH,
Bernie
MS Excel MVP
"Bernie Deitrick" <deitbe @ consumer dot org> wrote in message
news:%23yu8nGQzKHA.2436@TK2MSFTNGP04.phx.gbl...
> Two easy ways spring to mind: (change the 10000s to a number that is at
> least greater than your highest row)
>
> Add a new column A, and use 1,2,3, etc for each set. Then use this in
> D2:L9 (what used to be C2)
>
> =SUMIF($A$19:$A$10000,ROWS($A$1:A1),D$19:D$10000)
>
> Or, array enter (enter using Ctrl-Shift-Enter) this formula in C2
>
> =SUM(IF(MOD(ROW(C$19:C$10000)-ROWS(C$1:C4),15)=0,C$19:C$10000))
>
> and copy to C2:K9.
>
> For the VBA code, use the macro recored
>
> HTH,
> Bernie
> MS Excel MVP
>
>
> "DTTODGG" <DTTODGG@discussions.microsoft.com> wrote in message
> news:FA7732E2-2E93-44F7-A135-2E020DEB5FBD@microsoft.com...
>> Dave,
>>
>> Thanks for the "wrap".
>>
>> Bernie,
>>
>> I think I've got it. 15, 30, 45, 60, etc...
>> Is there a way to increment every 15th for however many "sets" of numbers
>> need totaling?
>>
>> 'Calculate
>>
>> Range("C2:K9").FormulaR1C1 = "=SUM(R[15]C,R[30]C)"
>>
>> Range("C12:K14").FormulaR1C1 = "=SUM(R[15]C,R[30]C)"
>>
>>
>>
>> "DTTODGG" wrote:
>>
>>> Bernie,
>>>
>>> Thank you for your quick reply.
>>> I have made a typo and the rows are actually every 15 rows apart. The
>>> TOTAL
>>> is on row 2. I'm adding row 17, 32, 47, 62, 77, etc.
>>>
>>> I tried your formula, but, I must be doing something wrong - could you
>>> help?
>>> I wanted to be able to read/verify my typing, so I would like to
>>> split/wrap
>>> the lines of code. I get an error. See below...and again, thank you.
>>>
>>> Range("C2:K9").FormulaR1C1 = "=SUM( _
>>> R[17]C, _
>>> R[32]C, _
>>> R[47]C, _
>>> R[62]C, _
>>> R[77]C, _
>>> R[92]C, _
>>> R[107]C, _
>>> R[122]C, _
>>> R[137]C, _
>>> R[152]C, _
>>> R[167]C, _
>>> R[182]C, _
>>> R[197]C, _
>>> R[212]C)"
>>>
>>> 'Bernie MS Excel MVP
>>>
>>>
>>>
>>>
>>>
>>> "Bernie Deitrick" wrote:
>>>
>>> > DTTODGG,
>>> >
>>> > No need to loop - the formulas that you are using are the same in RC,
>>> > so get
>>> > rid of the absolute addressing and enter it into all the cells at
>>> > once:
>>> >
>>> > Range("C2:D4").FormulaR1C1 = "=SUM(R[15]C,R[30]C,R[45]C,R[61]C)"
>>> >
>>> > So, to enter your formulas in every column from C to Z:
>>> > Range("C2:Z4").FormulaR1C1 = "=SUM(R[15]C,R[30]C,R[45]C,R[61]C)"
>>> >
>>> > And, by the way, your formula does NOT sum every fifteen row - the
>>> > last cell
>>> > is off by one row from that pattern. If the pattern held, you could
>>> > use a
>>> > different formula....
>>> >
>>> > By the way, if you wanted to loop:
>>> >
>>> > Sub Macro1()
>>> > Dim i As Integer
>>> > Dim j As Integer
>>> >
>>> > For i = 2 To 4
>>> > For j = 3 To 26
>>> > Cells(i, j).FormulaR1C1 = "=SUM(R" & i + 15 & "C" & j & _
>>> > ",R" & i + 30 & "C" & j & ",R" & i +
>>> > 45 &
>>> > "C" & j & _
>>> > ",R" & i + 61 & "C" & j & ")"
>>> > Next j
>>> > Next i
>>> > End Sub
>>> >
>>> > HTH,
>>> > Bernie
>>> > MS Excel MVP
>>> >
>>> >
>>>
>
>