Dynamic formula fill down

[tdcockers]

Wondering how to write a formula that will copy down (like dragging the fill handle) based on the length of a dynamic array? Unfortunately tables are not an option in this case.

In the below, all the formulas in row 4 are dynamic arrays of the same length. I'm trying to figure out an array formula in cell F4 that calculates YTD figures (based on lookup cell F3) for each row? My best guess was using SEQUENCE and OFFSET... until I found out OFFSET won't play in arrays.

sample.xlsx
	A	B	C	D	E	F
1		YTD
2		AUG	JUL	AUG	SEP	OCT
3
4	41100	-	-	-	-	-
5	41300		-	-	-	-
6	41400		-	-	-	-
7	41500		-	-	-	-
8	42300		71,405	53,300	-	130,313
9	43100		-	-	-	-
10	43200		-	-	48,004	16,079
11	44100		12,447	11,279	-	-
12	45050		-	-	-	-
13	45070		-	-	-	-
14	45100		79,261	4,248	1,075	1,075
15	45110		-	-	-	-
16	45120		-	-	-	-
17	45130		-	-	-	-
18	45140		-	-	-	-
Sheet1

Cell Formulas
Range	Formula
A4:A18,C4:F18	A4	=O30:O44
B4	B4	=SUM(OFFSET(B4,0,1):OFFSET(B4,0,XMATCH($B$2,$C$2:$F$2)))
Dynamic array formulas.

 

[Fluff]

I assume that you mean B4 & B2 rather than F4 & F3. If so try

=BYROW(TAKE(C4#,,XMATCH(B2,C2:F2)),LAMBDA(br,SUM(br)))

 

[tdcockers]

I assume that you mean B4 & B2 rather than F4 & F3

Yes I did, thank you... the other references are the cell numbers in my unsanitised file.

Formula works perfectly with one mod, the TAKE range needed to be C4#:F4# because each column is an individual array rather than a multi-array column. Thanks!

 

[Fluff]

You're welcome & thanks for the feedback.