How to copy from two cells, skip a cell, and copy from next two

[dmb41]

I am looking to extract data from one spreadsheet to input on another spreadsheet.

The issue is that it is two cells next to one another, then a blank cell, then two cells next to one another ... repeating

Is there a formula where I could skip the cell in between to gather this input?

What I am looking for is the following:

=SUM(HorzVert!A27:B27)

=SUM(HorzVert!D27:E27)

.
.
.

following the pattern AB, DE, GH, IJ, etc.

 

[West Man]

What cell will the first sum be in? Will you be dragging the formula down or accross?

 

[dmb41]

=SUM(HorzVert!N27:O27)

This will be dragged across to output Q & R, T & U, etc. (skips a letter/cell each time)

 

[West Man]

You did not say what cell the sum of A27:B27 would be in. I have assumed it to be A28. In A28 use


=SUM(HorzVert!A27:B27)
</PRE>in B28 and dragged across try: =SUM(OFFSET(HorzVert!A27,0,COLUMN()*2-1,1,2))

 

[dmb41]

I can get what you have provided to work on a test sheet when I start in cell A.

My specific example has the data in cells N27 & O27, Q27 & R27, and so on...

For simplicity, the on sheet which sums these two cells I can start in whichever cell , say N27.

=SUM(HorzVert!N27:N27)

I have tried altering what you have provided, but cant get it to work.
=SUM(OFFSET(HorzVert!N27,0,COLUMN()*2-1,1,2))
Does the column offset need to be adjusted because I am starting at N?

 

[West Man]

The offset does need to be adjusted based on where the sums begin. that is why I asked "What cell will the first sum be in?".

Based on starting in column N try: =SUM(OFFSET(HorzVert!$N27,0,COLUMNS($N:N)*3-3,1,2)) dragged across as needed.

 

[dmb41]

Thank you that is exactly what I was looking for. I have never used the offset function in that way before so I was altering the wrong part of it. I think its the the *3-3 part which I was inputting wrong.

Thanks for your time!

 

[West Man]

You are welcome. Glad it worked for you