# Excel - COLUMN Function Problem - Expert Solution

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
Excelchat Expert 29/09/2018 - 04:05
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
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
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
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.

Get instant expert help with Excel and Google Sheets

#### Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.