Reference problem by copying the formula down the row.

[Cy Sam]

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)?

 

[AlphaFrog]

Hi and welcome to the forum.

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

 

[Cy Sam]

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]

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]

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.