MAP / LAMBDA Functions to get multiple array elements

[MrRajKumar]

Hi,

I have the following data.

A2:C12

A	01K 03L 01	1-Jan-24​
B	09K 01L 00	1-Jan-24​
C	12K 01L 00	1-Jan-24​
D	00K 00L 01	1-Jan-24​
A	09K 00L 09	1-Jan-24​
A	10K 00L 01	1-Jan-24​
A	00K 30L 01	1-Jan-24​
B	02K 02L 02	1-Jan-24​
C	10K 10L 10	1-Jan-24​
D	11K 11L 10	1-Jan-24​
D	10K 00L 10	1-Jan-24 ​

E2:F4

A	1-Jan-24​
A	2-Jan-24​
B	4-Jan-24​

H2,

=SORT(UNIQUE(A2:A12))

L2: 1/1/2024
L3: 1/31/2024

L2:L3 is a month period.

In I2 I would like to get based on the following conditions.

1) If the dates in column F are in the month period (L2:L3) and take those dates for the values in H2:H5
2) Then, if these dates are in column C, then FILTER column B values for the values H2:H5
3) Then, SUM their values with help of MID function.

I tried the following, but doesn't work.

=SUM(MID(MAP(H2#,LAMBDA(x,FILTER(F2:F4,(E2:E4=x)*(F2:F4>=L2)*(F2:F4<=L3))),LAMBDA(x,y,FILTER(B2:B12,(A2:A12=x)*ISNUMBER(MATCH(C2:C12,y,0))))),{1,5,9},2)+0)

 

[MrRajKumar]

I get this to work for individual values in H2:H5

=SUM(MID(FILTER(B$2:B$12,(A$2:A$12=H2)*ISNUMBER(MATCH(C$2:C$12,FILTER(F$2:F$4,(E$2:E$4=H2)*(F$2:F$4>=L$2)*(F$2:F$4<=L$3)),0))),{1,5,9},2)+0)

But, the problem is; the range in column H is in spill. So how can I modify this include the spill range in H?

 

[KRice]

One idea:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1
2	A	01K 03L 01	1/1/2024		A	1/1/2024		A	65			1/1/2024
3	B	09K 01L 00	1/1/2024		A	1/2/2024		B	6
4	C	12K 01L 00	1/1/2024		B	1/4/2024		C	63
5	D	00K 00L 01	1/1/2024		C	1/1/2024		D
6	A	09K 00L 09	1/1/2024
7	A	10K 00L 01	1/1/2024
8	A	00K 30L 01	1/1/2024
9	B	02K 02L 02	1/4/2024
10	C	10K 10L 10	1/1/2024
11	D	11K 11L 10	1/1/2024
12	C	10K 00L 10	1/1/2024
Sheet1

Cell Formulas
Range	Formula
H2:I5	H2	=LET(codes,SORT(UNIQUE(A2:A12)),res,IFERROR(BYROW(codes,LAMBDA(r,SUM(MID(FILTER(B2:B12, ISNUMBER(MATCH(C2:C12,FILTER(F2:F5,(E2:E5=r)*(EOMONTH(+F2:F5,0)=EOMONTH(L2,0)),""),0))*(A2:A12=r),""),{1,5,9},2)+0))),""),HSTACK(codes,res))
Dynamic array formulas.

 

[Domenic]

I get this to work for individual values in H2:H5

=SUM(MID(FILTER(B$2:B$12,(A$2:A$12=H2)*ISNUMBER(MATCH(C$2:C$12,FILTER(F$2:F$4,(E$2:E$4=H2)*(F$2:F$4>=L$2)*(F$2:F$4<=L$3)),0))),{1,5,9},2)+0)

But, the problem is; the range in column H is in spill. So how can I modify this include the spill range in H?

It seems to me that you were almost there...

=MAP(H2#,LAMBDA(x,SUM(MID(FILTER(B$2:B$12,(A$2:A$12=x)*ISNUMBER(MATCH(C$2:C$12,FILTER(F$2:F$4,(E$2:E$4=x)*(F$2:F$4>=L$2)*(F$2:F$4<=L$3)),0))),{1,5,9},2)+0)))

Hope this helps!