# Reference non-consecutive cells

#### eugene7

##### Board Regular
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

### Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
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...

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

Steve

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

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

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?

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

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

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

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

Andrew,
Thanks, worked lika a charm!

Steve

Replies
12
Views
533
Replies
2
Views
162
Replies
0
Views
184
Replies
2
Views
119
Replies
4
Views
189

1,214,523
Messages
6,120,034
Members
448,940
Latest member
mdusw

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back