Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

Ex: =A1+A2 next cell down A3+A4 next cell down A5+A6, not A1+A2, A2+A3, A3+A4. I am trying to net cells, some are exact duplicates, some are not

share|improve this question
I’m not clear on what your objective is. Do you want a general solution to the question posed in the title, where successive rows in one column reference successive pairs of rows in another column, or are you just trying to solve the A1+A2, A3+A4, A5+A6 problem? I don’t understand “I am trying to net cells, some are exact duplicates, some are not”. – Scott Nov 14 '13 at 21:14

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

share|improve this answer

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 A3OFFSET(…, 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.

share|improve this answer

If the formula have some patterns (as per to your example =A1+A2, and then =A3+A4), then yes it is possible.

If you put (for example on B1) = A1+A2
and on B2 = A3+A4

highlight both B1 and B2, and then click and drag the bottom right corner of B2 (it should show as a thicker box) and drag and it will follow the pattern you have set previously.

If this is not to your need, you will need to update your questions with more details, and preferably some examples.

share|improve this answer
When I do that, it doesn't follow my pattern. If I highlight only B2 and drag it down I get, A1+A2, A2+A3, A3+A4. Then I tried highlighting both B1 & B2 and dragging it down and I get A3+A4, A3+A4, A5+A6, A5+A6, A7+A8, A7+A8.... – Reanna Nov 14 '13 at 4:55

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .