Relative reference autofill increment other than +1

[SteveB]

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.

 

[Biff]

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]

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]

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