Reference problem by copying the formula down the row.

Cy Sam

New Member
Hi, I have a reference problem by copying the formula down the row.

Here is my specific case:
I have number of values at Column A, I would like to sum up some of the values from Column A and store it in Column B. Example, values for A1, A2, A3, A4 are 2, 3, 2, 4 respectively, I want to store the sum of the 4 values into Column B cell B1. I have my formula B1 = sum(A1:A4), it will return the sum up value 11 and store into cell B1. Next, I want to store the next 4 cells in Column A in cell B2 as: B2 = sum(A5:A8). Same procedure applies by going down the Columns.

By doing this in a quick way, I copy the formula in B1 and apply it down of the column B by pulling down the bottom-right square symbol of the selected cell through the column. However, it is expected that the 2nd cell of Column B the formula is as B2=sum(A2:A5). The first reference cell of the formula will be the next of A1.

Question: How can I copy the formula down the Column B as cell B1=sum(A1:A4), B2=sum(A5:A8), B3=sum(A9:A12) and etc instead of cell B1=sum(A1:A4), B2=sum(A2:A5), B3=sum(A3:A6)?

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
Hi and welcome to the forum.

B1
=SUM(OFFSET(\$A\$1,(ROW()-1)*4,0,4))

Hi Alphafrog,
Thanks for your reply. Would you mind to kindly explain the coding above?
However, I went to compare my manual calculation and the coding you given, I unable to get the same result.
I wondering am I doing the correct steps.

The OFFSET function

Did you Change the formula?
Did you put the formula in B1?

Try this...
=SUM(OFFSET(\$A\$1,(ROW(\$A1)-1)*4,0,4))

Hi AlphaFrog,
Sorry, in my workfile I realized I am starting with B3.
I had find out the solution, so if in this case, I should change it into B3 =SUM(OFFSET(\$A\$1,(ROW()-3)*4,0,4)) right?
Thank you for your precious helping. You helped me in solving the troublesome problem I have facing.

Replies
5
Views
185
Replies
2
Views
123
Replies
2
Views
213
Replies
13
Views
163
Replies
5
Views
383

1,219,958
Messages
6,151,148
Members
451,011
Latest member
Pigdog89

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back