Reference non-consecutive cells

[eugene7]

Is there a quick way to reference non-consecutive cells?

For example:

SUM(A1)
SUM(A64)
SUM(A128)
SUM(A256)
...
SUM(A65536)

With each reference increasing in 64?

I really don't want to write the references by hand!

Ta,

Steve

 

[Oaktree]

Is it that you want every 64th row? Or that you want 2* the previous row?
Your pattern of 64-128-256 doesn't increment by 64's...

 

[eugene7]

Oops! How dumb of me...
I do want every 64th cell.

Steve

 

[Oaktree]

Try:

=SUMPRODUCT(--(MOD(ROW(A2:A65536)-ROW(A1)+1,64)=0),A2:A65536)

Note that you should tack on a +A1 to the end if you want to include cell A1 (since you want A128, A64, and A1, the A1 doesn't follow the 64th row pattern...)

 

[eugene7]

Hum, I'm getting a #NUM! error...

 

[eugene7]

Actually, the formula is being changes to:

=SUMPRODUCT(--(MOD(ROW(A:A)-ROW(A1)+1,64)=0),A:A)

when I hit return after entering your original formula?

 

[Oaktree]

I had a typo. Sumproduct cannot accept whole column references, so it should be A2:A65536 as in the corrected formula above.

 

[eugene7]

Thanks, no error now, but I'm not getting the values from the referenced cells in the formula cell.

Have I explained myself badly?

All I need is the actual value in the reference cells (so SUM is probably not necessary?)

So, I start with a column that's 65536 cells long, and end up with a column that's 1023 cells long with each cell having the value frome very 64th cell from the original column...

I think that explaines it better?

Thanks for the help; I really need to have this done in the next half hour!!!

Steve

 

[Andrew Fergus]

Hi Steve

Assuming the first item is in cell A1 and the next in cell A65 and so on, then use the following formula in Cell B1 and copy down :

=INDIRECT("A"&((ROW(A1)-1)*64)+1)

This will pull the value from every 64th cell starting at A1, then A65 and so on.

HTH, Andrew

 

[eugene7]

Andrew,
Thanks, worked lika a charm!

Steve