Relative reference autofill increment other than +1

I have often needed to drag and autofill a formula such that row references
increment by something more than 1. For example, consider the formula
"=sum(A1:A10)" in cell B1. If I have 100 rows of data in column A from A1 to
A100 and I want to roll up sums in groups of 10 starting in B1, I would drag
my formula down from B1 through to B10. The problem is that the autofill
increment will increment A1 to A2 and read "=sum(A2:A11)" and so on down the
rows. What I want the row reference increment to be is by 10 so the next
formula in B2 would read "=sum(A11:A20)" and so on down through B10 reading
"=sum(A91:A100).

Any ideas for how to control the row increment to something other than +1
when dragging down? I'll write a VBA procedure if I have to, but wonder if
there isn't something built in for this.

A solution to this would also have a complementary use for incrementing
columns in an autofill.

Hi!

Enter this formula in any cell in row 1 and copy down as needed:

=SUM(OFFSET(A$1,(ROW()-1)*10,,10))

B1 =Sum(A1:A10)
B2 =Sum(A11:A20)
B3 =Sum(A21:A30)
etc
etc

Biff

"SteveB" <[email protected]> wrote in message
news:[email protected]...
>I have often needed to drag and autofill a formula such that row references
> increment by something more than 1. For example, consider the formula
> "=sum(A1:A10)" in cell B1. If I have 100 rows of data in column A from A1
> to
> A100 and I want to roll up sums in groups of 10 starting in B1, I would
> drag
> my formula down from B1 through to B10. The problem is that the autofill
> increment will increment A1 to A2 and read "=sum(A2:A11)" and so on down
> the
> rows. What I want the row reference increment to be is by 10 so the next
> formula in B2 would read "=sum(A11:A20)" and so on down through B10
> reading
> "=sum(A91:A100).
>
> Any ideas for how to control the row increment to something other than +1
> when dragging down? I'll write a VBA procedure if I have to, but wonder
> if
> there isn't something built in for this.
>
> A solution to this would also have a complementary use for incrementing
> columns in an autofill.

Looks good and compact. I intend to code this into a VBA procedure for
right-click access to automate it for rows and columns. I'll use it a lot.

Thank you very much.

"Biff" wrote:

> Hi!
>
> Enter this formula in any cell in row 1 and copy down as needed:
>
> =SUM(OFFSET(A$1,(ROW()-1)*10,,10))
>
> B1 =Sum(A1:A10)
> B2 =Sum(A11:A20)
> B3 =Sum(A21:A30)
> etc
> etc
>
> Biff
>
> "SteveB" <[email protected]> wrote in message
> news:[email protected]...
> >I have often needed to drag and autofill a formula such that row references
> > increment by something more than 1. For example, consider the formula
> > "=sum(A1:A10)" in cell B1. If I have 100 rows of data in column A from A1
> > to
> > A100 and I want to roll up sums in groups of 10 starting in B1, I would
> > drag
> > my formula down from B1 through to B10. The problem is that the autofill
> > increment will increment A1 to A2 and read "=sum(A2:A11)" and so on down
> > the
> > rows. What I want the row reference increment to be is by 10 so the next
> > formula in B2 would read "=sum(A11:A20)" and so on down through B10
> > reading
> > "=sum(A91:A100).
> >
> > Any ideas for how to control the row increment to something other than +1
> > when dragging down? I'll write a VBA procedure if I have to, but wonder
> > if
> > there isn't something built in for this.
> >
> > A solution to this would also have a complementary use for incrementing
> > columns in an autofill.
>
>
>

One point to note,

The formula I posted is row specific and must be entered in a cell in row 1.

This formula is not row specific and can be entered anywhere in the sheet:

=SUM(OFFSET(A$1,(ROW(1:1)-1)*10,,10))

Biff

"SteveB" <[email protected]> wrote in message
news:[email protected]...
> Looks good and compact. I intend to code this into a VBA procedure for
> right-click access to automate it for rows and columns. I'll use it a
> lot.
>
> Thank you very much.
>
> "Biff" wrote:
>
>> Hi!
>>
>> Enter this formula in any cell in row 1 and copy down as needed:
>>
>> =SUM(OFFSET(A$1,(ROW()-1)*10,,10))
>>
>> B1 =Sum(A1:A10)
>> B2 =Sum(A11:A20)
>> B3 =Sum(A21:A30)
>> etc
>> etc
>>
>> Biff
>>
>> "SteveB" <[email protected]> wrote in message
>> news:[email protected]...
>> >I have often needed to drag and autofill a formula such that row
>> >references
>> > increment by something more than 1. For example, consider the formula
>> > "=sum(A1:A10)" in cell B1. If I have 100 rows of data in column A from
>> > A1
>> > to
>> > A100 and I want to roll up sums in groups of 10 starting in B1, I would
>> > drag
>> > my formula down from B1 through to B10. The problem is that the
>> > autofill
>> > increment will increment A1 to A2 and read "=sum(A2:A11)" and so on
>> > down
>> > the
>> > rows. What I want the row reference increment to be is by 10 so the
>> > next
>> > formula in B2 would read "=sum(A11:A20)" and so on down through B10
>> > reading
>> > "=sum(A91:A100).
>> >
>> > Any ideas for how to control the row increment to something other than
>> > +1
>> > when dragging down? I'll write a VBA procedure if I have to, but
>> > wonder
>> > if
>> > there isn't something built in for this.
>> >
>> > A solution to this would also have a complementary use for incrementing
>> > columns in an autofill.
>>
>>
>>