Mike:

Either subtract two from your final row count:

Cells(65536, 13).End(xlUp).End(xlUp).FormulaR1C1 = _
"=SUMIFS(R[0]C26:R[0]C45,INDIRECT(""$Z""&ROW(OFFSET(R[0]C,ROW(R1220C13)-ROW(R[0]C)-2,0))&"":""&""$AS""&ROW(OFFSET(R[0]C,ROW(R1220C13)-ROW(R[0]C)-2,0))),""k"")"

Or just move your row reference up by 2 rows:
Cells(65536, 13).End(xlUp).End(xlUp).FormulaR1C1 = _
"=SUMIFS(R[0]C26:R[0]C45,INDIRECT(""$Z""&ROW(OFFSET(R[0]C,ROW(R1218C13)-ROW(R[0]C),0))&"":""&""$AS""&ROW(OFFSET(R[0]C,ROW(R1218C13)-ROW(R[0]C),0))),""k"")"

HTH,
Bernie
MS Excel MVP


"MikeF" wrote in message
...
In the following code:

Cells(65536, 13).End(xlUp).End(xlUp).FormulaR1C1 = _

"=SUMIFS(R[0]C26:R[0]C45,INDIRECT(""$Z""&ROW(OFFSET(R[0]C,ROW(R1220C13)-ROW(R[0]C),0))&"":""&""$AS""&ROW(OFFSET(R[0]C,ROW(R1220C13)-ROW(R[0]C),0))),""k"")"

... Need the first row the offset formula is looking for, in this case
1220,
to *always* be -2, or in other works 2 rows above what 0 is.
Which in this case would indeed be 1220, but if r[0] is actually row 8 the
offset would look to row 6 to subtract row 8.

So in short, r1220 needs to be r-2 .... however that can be formulated.

Have tried many syntaxes that result in the routine stopping, but am sure
there is an easy answer.
If anyone can help, it would be sincerely appreciated.

Thanx,
- Mike