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.

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

"Jaxboo" wrote in message ...
|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.

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


"Jaxboo" wrote:

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.

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.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"Elkar" wrote in message
...
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


"Jaxboo" wrote:

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.

=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

On Tue, 9 Oct 2007 12:21:01 -0700, Jaxboo
wrote:

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.

Sorry, misread your question!

Fortunately you got some better answers

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Niek Otten" wrote in message ...
| 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
|
| "Jaxboo" wrote in message ...
||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.
|
|

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

"Elkar" wrote:

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


"Jaxboo" wrote:

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.

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


"Jaxboo" wrote:

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

"Elkar" wrote:

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


"Jaxboo" wrote:

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.

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?




"RagDyer" wrote:

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.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"Elkar" wrote in message
...
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


"Jaxboo" wrote:

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.

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)

$!
"Nick W" <Nick ...
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.

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$5:D5)-1)*7,,7))

--
Biff
Microsoft Excel MVP


"Nick W" <Nick wrote in message
...
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.

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.

"T. Valko" wrote:

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$5:D5)-1)*7,,7))

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$5:D5)-1)*7,,7))
to
=SUM(OFFSET(Sheet1!B$2,(ROWS(D$2:D2)-1)*7/4,,7))
--
David Biddulph


"Nick W" wrote in message
...
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.

"T. Valko" wrote:

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$5:D5)-1)*7,,7))

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.

--
Biff
Microsoft Excel MVP


"Nick W" wrote in message
...
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.

"T. Valko" wrote:

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$5:D5)-1)*7,,7))

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

"David Biddulph" <groups [at] biddulph.org.uk wrote in message
...
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$5:D5)-1)*7,,7))
to
=SUM(OFFSET(Sheet1!B$2,(ROWS(D$2:D2)-1)*7/4,,7))
--
David Biddulph


"Nick W" wrote in message
...
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.

"T. Valko" wrote:

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$5:D5)-1)*7,,7))

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.
--
David Biddulph

"T. Valko" wrote in message
...
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.

--
Biff
Microsoft Excel MVP


"Nick W" wrote in message
...
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.

"T. Valko" wrote:

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$5:D5)-1)*7,,7))

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.

--
Biff
Microsoft Excel MVP


"David Biddulph" <groups [at] biddulph.org.uk wrote in message
...
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.
--
David Biddulph

"T. Valko" wrote in message
...
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.

--
Biff
Microsoft Excel MVP


"Nick W" wrote in message
...
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.

"T. Valko" wrote:

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$5:D5)-1)*7,,7))

Well duh!

Disregard that previous post. It's dinner time!

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
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.

--
Biff
Microsoft Excel MVP


"David Biddulph" <groups [at] biddulph.org.uk wrote in message
...
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.
--
David Biddulph

"T. Valko" wrote in message
...
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.

--
Biff
Microsoft Excel MVP


"Nick W" wrote in message
...
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.

"T. Valko" wrote:

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$5:D5)-1)*7,,7))

Yes, it's been a long year. We're all getting tired! :-)
--
David Biddulph

T. Valko wrote:
Well duh!

Disregard that previous post. It's dinner time!


"T. Valko" wrote in message
...
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.

--
Biff
Microsoft Excel MVP


"David Biddulph" <groups [at] biddulph.org.uk wrote in message
...
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.
--
David Biddulph

"T. Valko" wrote in message
...
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.

--
Biff
Microsoft Excel MVP


"Nick W" wrote in message
...
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.

"T. Valko" wrote:

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$5:D5)-1)*7,,7))

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!

"T. Valko" wrote:

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$5:D5)-1)*7,,7))

--
Biff
Microsoft Excel MVP


"Nick W" <Nick wrote in message
...
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.

I have a question related to this topic also, how would you use this with the
function sumif & countif. These are the formulas I'm currently using,
SUMIF(H2:H73,"0",F2:F73)& COUNTIF(G2:G73,"0").

"Nick W" wrote:

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.

I have a similar issue but I am just trying to reference cells and not sum.
I have a row with several sets of data in in it that I want to consolidate
into a more vertical structure for analysis.

for example Row 1, columns A:AX, the first two columns stay the same and
then in sets of 6 columns I want to bring the data down for a new total of 8
rows:

A-B will remain the same for the following 8 rows
1. C1:H1
2. I1:N1
3. O1:T1
4: U1:Z1
5: AA1:AF1
6: AG1:AL1
7. AM1:AR1
8. AS1:AX1

I need to reapeat this this for rows 2-68 and likely more. While the time I
have spent trying to figure this out I could have copied and posted the whole
set. I am trying to find a fast, unbiased way to reduce human error.

Thanks in advance!

e


"Jaxboo" wrote:

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.