Hi,

Suppose I have a simple spread sheet where A1-A4,B1-B4 and C1-C4 have
various numbers.

A5 has the formula = SUM(A1:A4). B5 and C5 have similar formula to sum
their rows.

I now have a simple question.

I want to insert 4 rows after row 4 to enter new data into the 3
columns (12 cells). The formula which was in A5 will be moved to A9
(similar for cols B and C).

I can enter the rows OK by selecting 4 rows from A5 to A8 then
selecting the menus Insert / Rows.

However, when I do this, the formulae still refer to =SUM(A1:A4) and
not =SUM(A1:A8).

What is the best way to insert rows and adjust the formulae
automatically?

Thanks!

=SUM(OFFSET(A1,0,0,ROW()-1,1))


HTH,
JP

On Nov 4, 6:22 am, John Google wrote:
Hi,

Suppose I have a simple spread sheet where A1-A4,B1-B4 and C1-C4 have
various numbers.

A5 has the formula = SUM(A1:A4). B5 and C5 have similar formula to sum
their rows.

I now have a simple question.

I want to insert 4 rows after row 4 to enter new data into the 3
columns (12 cells). The formula which was in A5 will be moved to A9
(similar for cols B and C).

I can enter the rows OK by selecting 4 rows from A5 to A8 then
selecting the menus Insert / Rows.

However, when I do this, the formulae still refer to =SUM(A1:A4) and
not =SUM(A1:A8).

What is the best way to insert rows and adjust the formulae
automatically?

Thanks!

Keep the last row blank. Always include that in your sum formula. If
you insert/delete rows above the blank row, the formula will adjust
itself to included added/deleted rows.
AQIB RIZVI

On 4 Nov, 11:40, JP wrote:
=SUM(OFFSET(A1,0,0,ROW()-1,1))

HTH,
JP

On Nov 4, 6:22 am, John Google wrote:

Thanks JP. I like that solution as I don't like to leave blank rows. I
adjust the width / height of the column / row to provide my spacing.

John.

On 4 Nov, 11:41, Aqib Rizvi wrote:
Keep the last row blank. Always include that in your sum formula. If
you insert/delete rows above the blank row, the formula will adjust
itself to included added/deleted rows.
AQIB RIZVI

Thanks for your feedback AQIB. See the post by JP for a different
solution.

to sum to the row desired
=SUM(OFFSET($B$1,,,ROW()-1,1))
to sum to the last row with a number
=SUM(OFFSET($B$1,,,MATCH(99999,B:B),1))
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"John Google" wrote in message
oups.com...
Hi,

Suppose I have a simple spread sheet where A1-A4,B1-B4 and C1-C4 have
various numbers.

A5 has the formula = SUM(A1:A4). B5 and C5 have similar formula to sum
their rows.

I now have a simple question.

I want to insert 4 rows after row 4 to enter new data into the 3
columns (12 cells). The formula which was in A5 will be moved to A9
(similar for cols B and C).

I can enter the rows OK by selecting 4 rows from A5 to A8 then
selecting the menus Insert / Rows.

However, when I do this, the formulae still refer to =SUM(A1:A4) and
not =SUM(A1:A8).

What is the best way to insert rows and adjust the formulae
automatically?

Thanks!