Reference non-consecutive cells

eugene7

Board Regular
Joined
Apr 29, 2006
Messages
94
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...
 
Upvote 0
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...)
 
Upvote 0
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?
 
Upvote 0
I had a typo. Sumproduct cannot accept whole column references, so it should be A2:A65536 as in the corrected formula above.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top