In Excel, how do I copy a formula down or over, skipping cells?

Solution 1:

As a more general solution to Scott's answer, the following formula variant doesn't need to start in row 1:

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

To use it, enter the formula in same row as the first data point, adjust the absolute cell references to point to the first data point, and Auto Fill the formula down. E.g. to get the sum of A2+A3, A4+A5, etc., put the formula in B2 and point it to "$A$2", and drag/fill the formula down.

Similarly, to take the sum of three cells at a time (A1+A2+A3, A4+A5+A6, etc.), you can use

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

Solution 2:

If you are just trying to solve the A1+A2, A3+A4, A5+A6 problem, here’s a solution:  Enter

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

in cell B1 and drag/fill down.  OFFSET($A$1, 2*(ROW()-1), 0, … addresses a cell in Column A that steps down two rows every time the formula steps down one row.  For example, in B1, ROW() is 1 and this evaluates to OFFSET($A$1,0,0,… (cell A1), and in B2 it becomes OFFSET($A$1,2,0,…, i.e., cell A3.  OFFSET(…, 2, 1) identifies a region 2 rows high and one column wide.

A somewhat more general approach would be

=OFFSET($A$1, 2*(ROW()-1), 0, 1, 1)  +  OFFSET($A$1, 2*(ROW()-1)+1, 0, 1, 1)

or, equivalently,

=OFFSET($A$1, 2*(ROW()-1), 0, 1, 1)  +  OFFSET($A$2, 2*(ROW()-1), 0, 1, 1)

which explicitly references the two cells separately.