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

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

Oaktree

MrExcel MVP
Joined
Jun 20, 2002
Messages
8,010
Office Version
  1. 365
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...
 

Oaktree

MrExcel MVP
Joined
Jun 20, 2002
Messages
8,010
Office Version
  1. 365
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

Board Regular
Joined
Apr 29, 2006
Messages
94

ADVERTISEMENT

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

eugene7

Board Regular
Joined
Apr 29, 2006
Messages
94
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

MrExcel MVP
Joined
Jun 20, 2002
Messages
8,010
Office Version
  1. 365

ADVERTISEMENT

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

eugene7

Board Regular
Joined
Apr 29, 2006
Messages
94
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

MrExcel MVP
Joined
Sep 9, 2004
Messages
5,432
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
 

Forum statistics

Threads
1,136,700
Messages
5,677,277
Members
419,683
Latest member
MrVBAConfused

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
Top