# 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

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

#### AlphaFrog

##### MrExcel MVP
Hi and welcome to the forum.

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

#### Cy Sam

##### New Member
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.

#### AlphaFrog

##### MrExcel MVP
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))

#### Cy Sam

##### New Member
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
12
Views
592
Replies
3
Views
483
Replies
24
Views
644
Replies
2
Views
571
Replies
3
Views
972

### Forum statistics

1,191,025
Messages
5,984,201
Members
439,877
Latest member
kellylet ### 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.

### Which adblocker are you using?    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