need dynamic array formula

[Scott R]

I need a dynamic array formula in F3. The formula should return F3=3 and F4=10.
All arguments are dynamic arrays.
I was trying to avoid using OFFSET().

Book5
	A	B	C	D	E	F
1						total after
2		end date	1/1/24	2/1/24	3/1/24	end date
3	apple	2/1/2024	1	2	3	3
4	pear	1/1/2024	2	4	6	5
Sheet1

Cell Formulas
Range	Formula
C2:E2	C2	=DATE(2024,1,1)+DATE(,SEQUENCE(,3),)
B3:B4	B3	=EDATE(DATE(2024,2,1), {0;-1})
C3:E4	C3	=MAKEARRAY(2,3,LAMBDA(r,c, r*c))
F3:F4	F3	=SUMIF(C2#, ">" & B3#, C3#)
Dynamic array formulas.

 

[Scott R]

This seems more straightforward:

=BYROW( C3# * (C2# > B3#), LAMBDA(br, SUM( br )))

 

[Dave Patton]

Try the following.

Dynamic_Arrays.xlsm
	A	B	C	D	E		G
1							total after
2		end date	01-Jan-24	01-Feb-24	01-Mar-24		end date
3	apple	01-Feb-24	1	2	3		3
4	pear	01-Jan-24	2	4	6		10
5
1a

Cell Formulas
Range	Formula
C2:E2	C2	=DATE(2024,1,1)+DATE(,SEQUENCE(,3),)
B3:B4	B3	=EDATE(DATE(2024,2,1), {0;-1})
C3:E4	C3	=MAKEARRAY(2,3,LAMBDA(r,c, r*c))
G3:G4	G3	=BYROW(C3#*(C2# >B3#),SUM)
Dynamic array formulas.

 

[Fluff]

How are you able to pass in C2# (an array)

C2# is a range not an array.

 

[Cubist]

C2# is a range not an array.

Then, I'm confused about what being a range vs array means. I was under the impression that C2# with the hash symbol is a spilled array.

 

[Fluff]

Whilst the formula in C3 is an array, C3# is a spilled range.

 

[Cubist]

Whilst the formula in C3 is an array, C3# is a spilled range.

If I understand correctly, I can't pass it in like this,

=BYROW(B3#:C3,LAMBDA(br,SUMIF(MAKEARRAY(2,3,LAMBDA(r,c, r*c)),">"&INDEX(br,,1),DROP(br,,1))))

but the result of the array formula is a range, which is acceptable.

 

[Fluff]

That's right.