Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help adding text values | Excel Worksheet Functions | |||
Convert data type of cells to Text,Number,Date and Time | Excel Worksheet Functions | |||
To safety merge cells without data destroyed, and smart unmerge! | Excel Discussion (Misc queries) | |||
Heps to design Locked/Unlocked cells in protected worksheet | Excel Discussion (Misc queries) | |||
Convert data of cells to any type: Number, Date&Time, Text | Excel Discussion (Misc queries) |