Sum Offset() Array Question

[NateO]

Hola Excel gurus, this is my first OP question on the board, how exciting. In any case here's the deal, I'm trying to sum o12:q12, and every 7th offest row of this range through row 222. Here's what I thought would do the trick:

=SUM(N(OFFSET(O11,(ROW(1:31)-1)*7+1,0,1,3)))

It's returning the correct range, and I believe I'm following the dereferencing *rules*. But it's not working, it's only giving me the first cell in the array, when I expect the full array. I've tried transposing this once and a few times, as the VB results of this array are odd, still no dice. Is the three column array bollixing this up or am I missing something obvious?

Thanks in advance.

Edit: {=SUM(TRANSPOSE(OFFSET(O11,(ROW(1:31)-1)*7+1,0,1,3)))} and
{=SUM(N((OFFSET(O11,(ROW(1:31)-1)*7+1,0,1,3))))}
Gives me all of cells in column O, but not the three columns...

Edit: Still interested obviously, but I'll use a udf in the interim which actually works:

<font face=Courier New><SPAN style="color:#00007F">Function</SPAN> foobar() <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Currency</SPAN>
<SPAN style="color:#00007F">Dim</SPAN> y <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Variant</SPAN>, z <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, Cls <SPAN style="color:#00007F">As</SPAN> Range, ws <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>
ws = Application.Caller.Parent.Index
y = [transpose(OFFSET(o11,(row(1:31)-1)*7+1,0,1,3))]
<SPAN style="color:#00007F">Set</SPAN> Cls = Sheets(ws).Range(y(1).Address)
<SPAN style="color:#00007F">For</SPAN> z = 2 <SPAN style="color:#00007F">To</SPAN> <SPAN style="color:#00007F">UBound</SPAN>
   <SPAN style="color:#00007F">Set</SPAN> Cls = Union(Cls, Sheets(ws).Range(y(z).Address))
<SPAN style="color:#00007F">Next</SPAN>
foobar = Application.Sum(Cls)
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Function</SPAN></FONT>

With usage: =foobar()

I hate Excel.

 

[Juan Pablo González]

Nate,

As Aladin recently discovered, you have to use SUBTOTAL in this case...

=SUMPRODUCT(SUBTOTAL(9,OFFSET(INDEX(Rng,1,1),(ROW(Rng)-MIN(ROW(Rng)))*Offset,0,1,COLUMNS(Rng))))

for the "generic" version to SUM a range called 'Rng' using an offset of 'Offset' cells.

 

[Aladin Akyurek]

Just to make sure... You want to sum O12:Q12, O19:Q19, O26:Q26, etc. Right?

 

[NateO]

Yes Aladin, that is indeed the correct range pattern, through row 222.

Thanks Juan, I shall tinker with that concept. Subtotal eh, hmmm, should read more I suppose. :wink:

 

[Aladin Akyurek]

Yes Aladin, that is indeed the correct range pattern, through row 222.

Thanks Juan, I shall tinker with that concept. Subtotal eh, hmmm, should read more I suppose. :wink:

=SUMPRODUCT((MOD(ROW($O$12:$O$222)-CELL("Row",$O$12:$O$222)+0,7)=0)*($O$12:$Q$222))

 

[Andrew Poulsom]

I think the use of the ROW function in:

{=SUM(N(OFFSET(O11,(ROW(1:31)-1)*7+1,0,1,3)))}

constrains you to a single column because it is only returning the equivalent of items 1 to 31 in the array - that is the entries in the first column when entered as an array formula.

This worked for me:

=SUMPRODUCT((MOD(ROW(O12:Q222),7)=5)*(O12:Q222))

 

[NateO]

=SUMPRODUCT((MOD(ROW($O$12:$O$222)-CELL("Row",$O$12:$O$222)+0,7)=0)*($O$12:$Q$222))

Bingo! And it makes sense too, which is nice. No offset() then eh, thought that looked liked a straight-forward approach, guess not.

Thanks!

 

[NateO]

I think the use of the ROW function in:

{=SUM(N(OFFSET(O11,(ROW(1:31)-1)*7+1,0,1,3)))}

constrains you to a single column because it is only returning the equivalent of items 1 to 31 in the array - that is the entries in the first column when entered as an array formula.

I guess I thought the 3 argument would resize o to q though, like testing the following:

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> jdsfhjuhds()
<SPAN style="color:#00007F">Dim</SPAN> y <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Variant</SPAN>, z <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, Cls <SPAN style="color:#00007F">As</SPAN> Range
y = [transpose(OFFSET(o11,(row(1:31)-1)*7+1,0,1,3))]
<SPAN style="color:#00007F">Set</SPAN> Cls = y(1)
<SPAN style="color:#00007F">For</SPAN> z = 2 <SPAN style="color:#00007F">To</SPAN> <SPAN style="color:#00007F">UBound</SPAN>
   <SPAN style="color:#00007F">Set</SPAN> Cls = Union(Cls, y(z))
<SPAN style="color:#00007F">Next</SPAN>
Debug.Print Cls.Address
MsgBox y(UBound).Address
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

The string is limited to 145, but if you grab the Ubound() of y, you can see it running out until 222. I thought this should've worked conceptually... Very good though, sumproduct it is!

Thanks again.