Array adjusting location by a variable

[Calgary_Neil]

I'm trying to get a formula that will check an array of 8 cells for "Y", and if true sum the adjacent (-1) cells, and move the arrays X*7 columns over.
A static formula that works is SUMIF( X2:X9, "=Y", W2:W9) = 15 where the X is 4 (7*4-5). The problem with this formula it doesn't adjust when a new group of 7 is placed at the end (ie AY) extending the range.
Using Offset is dynamic which I'm trying to avoid.

Demo.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY
1		1							2							3							4							5							6							7
2		10	Y						10							15							20							20							20							30
3		20							20							25							25							25							20							20	Y
4		20							20							30							25							25							10	Y						10
5		20							20							30	Y						30							30							25							25
6		15							15	Y						15							15							15							15							15
7		20							20							25							25							25							0							0
8		15							15							15							15	Y						15							0							0
9		20							20							25							25							25	Y						0							0
Sheet2

 

[GlennUK]

You are describing something that seems to suit OFFSET, so please explain in detail why that is to be avoided?

 

[Calgary_Neil]

Offset, Indirect, a few others are dynamic and are coded to solve each time the user presses enter (or so I understand). To many can really slow a sheet down, so I just avoid them in production work.

 

[Calgary_Neil]

I am currently using offset for this problem. I don’t work in arrays much, so I really don’t know how to create them without it.

 

[Fluff]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

How about

=LET(c,LOOKUP(2,1/(B1:ZZ1<>""),COLUMN(B1:ZZ1))-22,SUMIFS(INDEX(B2:ZZ9,,c),INDEX(B2:ZZ9,,c+1),"y"))