Sum only last X cells
 

I have a worksheet where I add every week a column with numbers. I insert a
new column for the new week and the next cell has a formula that sums only
the last four cells.

The thing is that when I insert the column, the sums stays refering to the
previous 4 and does not consider the new one. I need this sum to consider
ONLY the last 4 coulmns to te left of that, any ideas? Can you refer
dynamicaly to the previous 4 cells in order to sum them??

Thanks in advance,
Rds, from Argentina
Ignacio

Hi Ignacio,

This works for row 1, assuming that the data starts in A1

=SUM(OFFSET(A1,,COUNTA(1:1)-4,,4))

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Nacho" wrote in message
...
I have a worksheet where I add every week a column with numbers. I insert
a
new column for the new week and the next cell has a formula that sums only
the last four cells.

The thing is that when I insert the column, the sums stays refering to the
previous 4 and does not consider the new one. I need this sum to consider
ONLY the last 4 coulmns to te left of that, any ideas? Can you refer
dynamicaly to the previous 4 cells in order to sum them??

Thanks in advance,
Rds, from Argentina
Ignacio

Let me give an example:

I have the total in the right,

A B C D E
1 8 1 5 15

So when i add a column between E and D, i want the sum to be from B to E

Clearer now?

Rds, and many thanks
Ignacio

"Bob Phillips" wrote:

Hi Ignacio,

This works for row 1, assuming that the data starts in A1

=SUM(OFFSET(A1,,COUNTA(1:1)-4,,4))

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Nacho" wrote in message
...
I have a worksheet where I add every week a column with numbers. I insert
a
new column for the new week and the next cell has a formula that sums only
the last four cells.

The thing is that when I insert the column, the sums stays refering to the
previous 4 and does not consider the new one. I need this sum to consider
ONLY the last 4 coulmns to te left of that, any ideas? Can you refer
dynamicaly to the previous 4 cells in order to sum them??

Thanks in advance,
Rds, from Argentina
Ignacio

where e1 is the location of this formula
=SUM(OFFSET(E1,0,-4,1,4))

"Nacho" wrote:

I have a worksheet where I add every week a column with numbers. I insert a
new column for the new week and the next cell has a formula that sums only
the last four cells.

The thing is that when I insert the column, the sums stays refering to the
previous 4 and does not consider the new one. I need this sum to consider
ONLY the last 4 coulmns to te left of that, any ideas? Can you refer
dynamicaly to the previous 4 cells in order to sum them??

Thanks in advance,
Rds, from Argentina
Ignacio

That is nothing like what you originally asked.

=SUM(OFFSET(A1,,COUNT(A1:OFFSET(E1,,-1))-4,,4))

"Nacho" wrote in message
...
Let me give an example:

I have the total in the right,

A B C D E
1 8 1 5 15

So when i add a column between E and D, i want the sum to be from B to E

Clearer now?

Rds, and many thanks
Ignacio

"Bob Phillips" wrote:

Hi Ignacio,

This works for row 1, assuming that the data starts in A1

=SUM(OFFSET(A1,,COUNTA(1:1)-4,,4))

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Nacho" wrote in message
...
I have a worksheet where I add every week a column with numbers. I
insert
a
new column for the new week and the next cell has a formula that sums
only
the last four cells.

The thing is that when I insert the column, the sums stays refering to
the
previous 4 and does not consider the new one. I need this sum to
consider
ONLY the last 4 coulmns to te left of that, any ideas? Can you refer
dynamicaly to the previous 4 cells in order to sum them??

Thanks in advance,
Rds, from Argentina
Ignacio