How do I copy a formula with the cell increasing by increments?

[Guest]

I would like to take a simple Sum formula such as Sum(b1:b6) and copy it into
the next cell in an increment so the next cell (below) will read sum(b7:b12)

How would I do this? I have a large number of cells I will be doing this
with and really do not want to have to go back and forth between worksheets
to select the cells.

 

[Niek Otten]

Just drag the cell down. Place your cursor on the little square in the right bottom corner of the cell, press and hold the left
mouse button, move the mouse to the last destination cell (still keeping the mouse button pressed) and once you're there, release
it.

You can also copy and paste via the menu; Excel will adjust the cell references automatically.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

|I would like to take a simple Sum formula such as Sum(b1:b6) and copy it into
| the next cell in an increment so the next cell (below) will read sum(b7:b12)
|
| How would I do this? I have a large number of cells I will be doing this
| with and really do not want to have to go back and forth between worksheets
| to select the cells.

 

[Guest]

You can use the INDIRECT and ROW functions to do this. For example, assuming
your first formula will be placed in Row 1, you'd use:

=SUM(INDIRECT("B"&ROW()*6-5&":B"&ROW()*6))

HTH,
Elkar

 

[RagDyer]

A non-volatile approach:

=SUM(INDEX(B:B,6*ROWS($1:1)-5):INDEX(B:B,6*ROWS($1:1)))

And copy down as needed.

 

[Gord Dibben]

=SUM(INDEX(B:B,ROW(B1)*6-5):INDEX(B:B,ROW(B1)*6))

Enter in C1 and drag/copy down until you get zeros.


Gord Dibben MS Excel MVP

 

[Niek Otten]

Sorry, misread your question!

Fortunately you got some better answers

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| Just drag the cell down. Place your cursor on the little square in the right bottom corner of the cell, press and hold the left
| mouse button, move the mouse to the last destination cell (still keeping the mouse button pressed) and once you're there,
release
| it.
|
| You can also copy and paste via the menu; Excel will adjust the cell references automatically.
|
| --
| Kind regards,
|
| Niek Otten
| Microsoft MVP - Excel
|
||I would like to take a simple Sum formula such as Sum(b1:b6) and copy it into
|| the next cell in an increment so the next cell (below) will read sum(b7:b12)
||
|| How would I do this? I have a large number of cells I will be doing this
|| with and really do not want to have to go back and forth between worksheets
|| to select the cells.
|
|

 

[Guest]

How would the indirect function be changed to reflect data on a different
spreadsheet?

 

[Guest]

For a different sheet? You'd just add the sheet name inside of the INDIRECT
function, like this:

=SUM(INDIRECT("'Sheet Name'!B"&ROW()*6-5&":B"&ROW()*6))

However, if you're asking about a different workbook, then that may become a
problem. The INDIRECT function only works on different workbooks if they are
open. In this case, I'd suggest using RagDyer's or Gord Dibben's solutions
instead.

HTH,
Elkar

 

[dgb82]

In regards to what Jaxboo wrote below:

To start RagDyer, you were correct on Jaxboo question, but now I have more
difficult problem.

I would like to take a simple Sum formula such as Sum(b1:e6) and copy it
into
the next cell in an increment so the next cell (below) will read
sum(b7:e12)

How would you answer the above?

 

[Nick W]

I have a very similar situation to this but knowing very little about excel
functions I am hoping someone can help me understand the changes I need to
make to get it to work.

Working with 2 sheets (sheet1 and sheet2 to make it easier)
Second sheet references the first, formula starts in B2 of sheet2

Formula needs to get the SUM of B5:B11
when copied down it needs to then get the SUM of B12:18 then B19:B25, etc

I tried to manipulate the functions listed above, but they always seem to
pick odd cells to start from, or pick weird increments to go up by. Any help
would be greatly appreciated!! Thanks in advance.

 

[Ë­ÊÇË­]

Do you mean:
sum($B$5:B11)
sum($B$12:B18)
sum($B$19:B25)

$!

 

[T. Valko]

Assume you want the results to appear in D5 downwards.

Enter this formula in D5 and copy down as needed:

=SUM(OFFSET(B$5,(ROWS(D$55)-1)*7,,7))

 

[Nick W]

I should have been a little more specific, sorry. The formula is starting in
B2 of sheet 2. So I want it to be Sum('sheet1"!B5:B11) when it is copied
down to B6 in Sheet 2, it will give the SUM('sheet1'!B12:B18).

I appologize, my syntax is terrible because I really haven't been in a
position that required me to use Excel for more than standard data entry
purposes. I thought that grouping sums would be pretty straight forward. So
far from what I have learned just trying to get this to work, I am amazed at
the things I can do with this program.

 

[David Biddulph]

If you want the input cells to shift down 4 rows when you copy your formula
down 4 rows, just change T Valko's
=SUM(OFFSET(B$5,(ROWS(D$55)-1)*7,,7))
to
=SUM(OFFSET(Sheet1!B$2,(ROWS(D$22)-1)*7/4,,7))

 

[T. Valko]

Enter this formula in B2:

=SUM(Sheet1!B5:B11)

Enter this formula in B6:

=SUM(Sheet1!B12:B18)

Select the range B2:B9 and drag copy down as needed.

 

[David Biddulph]

Should, of course, have been
=SUM(OFFSET(Sheet1!B$5,(ROWS(D$22)-1)*7/4,,7))

 

[David Biddulph]

Didn't work for me.
B10 shows =SUM(Sheet1!B13:B19)
B14 shows =SUM(Sheet1!B20:B26)
B18 shows =SUM(Sheet1!B21:B27)
so alternately stepping 1 row & 7 rows.
I think your original OFFSET formula was the right way, modified for the
OP's changed requirements.

 

[T. Valko]

Works for me (Excel 2002). I get:

B2: =SUM(Sheet1!B5:B11)
B6: =SUM(Sheet1!B12:B18)
B10: =SUM(Sheet1!B13:B19)
B14: =SUM(Sheet1!B20:B26)
B18: =SUM(Sheet1!B21:B27)

And all cells in-between are empty.

 

[T. Valko]

Well duh!

Disregard that previous post. It's dinner time!

 

[Nick W]

The offset funtion worked perfectly. Thank you so much! I had to do a
little tweaking on a few of the cells, but once I understood how the
variables worked it was a snap. Thank you everyone, this will make future
work a breeze!