Excel copy down formula

Question

I have a table of data and in a separate column I want the sum of every 2 lines of one of the columns: eg +P1+P2, +P3+P4, +P5+P6 etc. There are too many lines to enter them all manually, and even if there weren't I always like the quickest way. But if copy the formula down I'm getting lots of fun and interesting patterns but none are what I want.

I tried entering +P1+P2 and dragged down but the next lines were +P2+P3, P3+P4, P4+P5 etc

I tried entering +P1+P2, +P3+P4 and dragged down but the next lines were +P3+P4, +P5+P6, +P5+P6, +P7+P8, +P7+P8 etc

I tried entering +P1+P2, +P3+P4, +P5+P6 and dragged down but the next lines were +P4+P5, +P6+P7, +P8+P9, +P7+P8, +P9+P10 etc - argh

There must be a way to get the formula to carry the pattern down the way I want it, any suggestions? Please!

Answer 1

Hi,

In case You want the sum-of-2-lines to appear in consecutive rows, You may consider following solution:

- In the screenshot below, numbers are in column P > starting cell P2 onwards.

Formula in cell Q2 is: =SUM(INDIRECT("P"&ROW(P2)*2-2),INDIRECT("P"&ROW(P2)*2-1))

- Please drag the formula down to more rows.

- In case Your numbers are in any other column and do not start from row#2, You are only required to modify the bold part in the above formula.

- "P" - is the column that has the numbers.

- P2 - is the 1st cell that has the number.

- 2 - is the row number of the 1st cell that has the number.

- 1 - is the row number of the 1st cell that has the number minus 1.

Please respond if You require further assistance. I will try My best to be of help.

If I was able to help You, please mark My response as answer and helpful.

Thank You!

Answer 2

Thanks everyone, I have not responded back because every attempt to log back in until now failed!

The spreadsheet is on my work laptop which I left at work so will attempt to log back in again tomorrow when I'm back in the office.

Answer 3

Hi ,

I am following up on this thread to know if you have seen the provided suggestions. Feel free to share updates at your convenience time.

Regards,

Christophe

Answer 4

Hi there

Let's say row 1 is for the headers

Your values are in cell P2 downwards

You may try the formula in cell Q2=

IF(ISEVEN(ROWS($P$2:P2)),SUM(P1:P2),"")

And copy/drag it down.

Do let me know if you need more help

Regards

Jeovany