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.

 

[Fluff]

How about

=BYROW(B3#:C3,LAMBDA(br,SUMIF(C2#,">"&INDEX(br,,1),DROP(br,,1))))

 

[Scott R]

Clever! I'm puzzled how it works with BYROW(B3#:C3, ). I would've thought it had to be BYROW(B3#:C3#, ) ?

 

[Fluff]

It will spill down as far as col B regardless of whether you use C3 or C3#

 

[Scott R]

How about horizontally though. How does B3#:C3 pick up E4, for example, in the DROP portion?
Doesn't B3#:C3 literally mean B3:C4?

 

[Fluff]

Doesn't B3#:C3 literally mean B3:C4?

Yes it does, but because of the C2# in the sumif it looks at C3:E3 & then C4:E4

 

[Scott R]

Did not know that it would essentially coerce the sum range. Thx!

 

[Fluff]

You're welcome & thanks for the feedback.

 

[Cubist]

@Fluff
How are you able to pass in C2# (an array) into SUMIF?

 

[Scott R]

Any idea why this works:

=BYROW( B3#:C3#, LAMBDA(br, SUMIF( C2#, ">" & INDEX( br,, 1 ), DROP( br,, 1 ))))

but this doesn't:

=BYROW( HSTACK(B3#,C3#), LAMBDA(br, SUMIF( C2#, ">" & INDEX( br,, 1 ), DROP( br,, 1 ))))

They seem equivalent...