Question description:
This user has given permission to use the problem statement for this
blog.
I need cell C2 to be the sum of cells B2:B8, then cell C3 to be the sum of cells B9:B15, and so on. Is there an easy way of copying the formula down column C to account for the changing cell range in column B?
Solved by O. W. in 35 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
29/09/2018 - 04:04
Hello
Excelchat Expert
29/09/2018 - 04:05
Welcome to Excelchat, I see that your question is about formula.
Excelchat Expert
29/09/2018 - 04:05
I can help you with that problem.
Excelchat Expert
29/09/2018 - 04:05
Before we get started, this is a reminder that our policy is 1 problem per session with additional Q&A on that problem as time allows.
User
29/09/2018 - 04:05
understood
Excelchat Expert
29/09/2018 - 04:05
Thank you.
Excelchat Expert
29/09/2018 - 04:05
Let me ask you a couple of quick questions to make sure I fully understand your problem.
User
29/09/2018 - 04:06
ok
Excelchat Expert
29/09/2018 - 04:07
So C2 should be Sum (B2:B8) and when you want to copy it to C3 it should become Sum of B3 to B9?
User
29/09/2018 - 04:08
no. currently if i just use the mass copy function it changes C3 to be the sum of B3:B9 but i want it to be B9:B15
User
29/09/2018 - 04:09
then C4 would be the sum of B16:B22 and so on
Excelchat Expert
29/09/2018 - 04:09
Ok. So always 7 cells
User
29/09/2018 - 04:10
yes, always 7 cells
Excelchat Expert
29/09/2018 - 04:10
Ok.
Excelchat Expert
29/09/2018 - 04:10
I’m going to make that formula for you. This should take me few minutes to solve. If prompted for extension of session, please do so.
User
29/09/2018 - 04:10
will do
Excelchat Expert
29/09/2018 - 04:10
Thank you.
Excelchat Expert
29/09/2018 - 04:14
=Sum(offset($B$2,(ROW()-ROW($B$2))*7,0,7.1))
Excelchat Expert
29/09/2018 - 04:15
Here it is.
Excelchat Expert
29/09/2018 - 04:15
Can you see the preview on right?
Excelchat Expert
29/09/2018 - 04:15
I have used the formula in the C2
Excelchat Expert
29/09/2018 - 04:15
and then copied it down to C3
Excelchat Expert
29/09/2018 - 04:16
and we can copy this down further it will keep adding next 7 rows.
User
29/09/2018 - 04:17
it worked great! thank you so much!!
Excelchat Expert
29/09/2018 - 04:17
Do you want to understand how this formula is working?
Excelchat Expert
29/09/2018 - 04:18
=Sum(offset($B$2,(ROW()-ROW($B$2))*7,0,7.1))
User
29/09/2018 - 04:18
not quite
Excelchat Expert
29/09/2018 - 04:18
Ok.
Excelchat Expert
29/09/2018 - 04:18
Does this solution solve your problem?
User
29/09/2018 - 04:18
yes, this did solve my question
Excelchat Expert
29/09/2018 - 04:18
Please feel free to ask me any other questions about this problem and the solution?
Excelchat Expert
29/09/2018 - 04:19
Thanks for coming to Excelchat.
Excelchat Expert
29/09/2018 - 04:19
Also, if you are satisfied with my solution, please go ahead and end the session using the button on the top right hand side of this chat window and requesting you to provide good feedback and review so that I can continue my services to others.
User
29/09/2018 - 04:20
my only other question is if i wanted to use the formula you provided, but reference another tab in the document where would that be inputted?
User
29/09/2018 - 04:20
for instance if the tab i wanted to copy the cells from was on a tab named Daily
Excelchat Expert
29/09/2018 - 04:20
so instead of B2 in the same sheet you want to put that cell from another sheet.
Excelchat Expert
29/09/2018 - 04:21
So first of all we have to know which is first cell we want to add.
Excelchat Expert
29/09/2018 - 04:21
like in this it is B2
User
29/09/2018 - 04:22
so it would also be cell B2 buy on the other tab
Excelchat Expert
29/09/2018 - 04:22
Ok
Excelchat Expert
29/09/2018 - 04:22
Can you see the preview on right?
User
29/09/2018 - 04:22
yes
Excelchat Expert
29/09/2018 - 04:23
=Sum(offset($B$2,(ROW()-ROW($B$2))*7,0,7.1))
Excelchat Expert
29/09/2018 - 04:23
=Sum(offset(Sheet2!$B$2,(ROW()-ROW(Sheet2!$B$2))*7,0,7.1))
Excelchat Expert
29/09/2018 - 04:23
so $B$2 becomes Sheet2!$B$2
Excelchat Expert
29/09/2018 - 04:24
and rest everything remains same.
User
29/09/2018 - 04:25
ah ha!
User
29/09/2018 - 04:25
i see.
User
29/09/2018 - 04:26
so now that i understand the formula and it manipulation, what does the *7,0,7.1 part mean
Excelchat Expert
29/09/2018 - 04:26
Ok. Let me explain
User
29/09/2018 - 04:26
i really do appreciate it
Excelchat Expert
29/09/2018 - 04:28
offset(reference, row, column,height width)
Excelchat Expert
29/09/2018 - 04:28
B2 indicates the started row number you want to sum, and 7 stands for the incremental row numbers. If you want to sum every 10 rows in column B, you just need to change 7 to 10.
Excelchat Expert
29/09/2018 - 04:29
Offsets basically shift the cell
Excelchat Expert
29/09/2018 - 04:30
Row() will give the row number of the cell in which we have the formula
Excelchat Expert
29/09/2018 - 04:31
ROW($B$2) will always give the row number of the first cell of the data i.e. from where the data begins
Excelchat Expert
29/09/2018 - 04:32
So Column G is the value we get from Row()-Row($b$2)
Excelchat Expert
29/09/2018 - 04:32
and then multiplying it by 7 to shift it 7 rows
User
29/09/2018 - 04:33
I'm following so far
Excelchat Expert
29/09/2018 - 04:33
So this gives the first cell from where we want to chart the Sum
Excelchat Expert
29/09/2018 - 04:34
So for C2 it is 0 and C3 it will shift the cell to 7 places to find the first cell.
Excelchat Expert
29/09/2018 - 04:35
For C2 - it is C2 for C3 it becomes 9
Excelchat Expert
29/09/2018 - 04:35
and so on.
Excelchat Expert
29/09/2018 - 04:35
and then column in formula is 0 as we want to keep it in the same column
Excelchat Expert
29/09/2018 - 04:35
then again to find the final cell to add we shift the first found cell with 7
User
29/09/2018 - 04:36
what about the 7.1?
Excelchat Expert
29/09/2018 - 04:36
and last1 is inconsequential we can delete that too and it will work the same.
Excelchat Expert
29/09/2018 - 04:37
7 is the one which we are again shifting the first cell identified to 7 places.
User
29/09/2018 - 04:37
oh ok, thats what was really confusing me but now makes sense
Excelchat Expert
29/09/2018 - 04:37
My apologies.
Excelchat Expert
29/09/2018 - 04:38
Do you have any other questions about this problem and the solution?
User
29/09/2018 - 04:38
that was all. I really do appreciate your help and explaining it to me
Excelchat Expert
29/09/2018 - 04:39
The pleasure was all mine.
Excelchat Expert
29/09/2018 - 04:39
Thanks for coming to Excelchat.
Excelchat Expert
29/09/2018 - 04:39
Also, if you are satisfied with my solution, please go ahead and end the session using the button on the top right hand side of this chat window and requesting you to provide good feedback and review so that I can continue my services to others.
This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user
information.
