FILTER Function & MAP/LAMBDA through Different Ranges & Arrays

[MrRajKumar]

Hi,

I have the following data. Places are unique, no duplicate.

A1:C10

Item	Place	Status
A	Place 1	Active
A	Place 2	Inactive
B	Place 3	Active
B	Place 4	Active
A	Place 5	Active
A	Place 6	Active
C	Place 7	Active
C	Place 8	Active
C	Place 9	Active

E1:G9

Place	Status	Date
Place 1	Off	3/1/2024​
Place 1	Good	2/25/2024​
Place 2	Good	3/25/2024​
Place 4	Good	1/1/2024​
Place 6	Off	1/1/2024​
Place 7	Away	2/1/2024​
Place 9	Off	1/1/2024​
Place 9	Good	2/1/2024​

Then, I have the following.

I2, to get unique Items

=UNIQUE(A2:A10)

J2, to get count of active places in each items.

=COUNTIFS(A2:A10,I2#,C2:C10,"Active")

In k2, i would like to get the count based on the following conditions. I would like to get the formula that support dynamic array.

1). Take 'Active' places from B2:B10 of the corresponding Item.
2). Look their 'Status' from F2;F9. if 'Status' is "<>Off"
3). Date in G2:G9 is <=Today().

Here is the final count.

A	1​
B	1​
C	2​

I tried the following formula to get corresponding array of places, but no luck.

=MAP(I2#,LAMBDA(a,FILTER(B2:B10,A2:A10=a)))

 

[Fluff]

How about

=MAP(I2#,LAMBDA(m,SUM(COUNTIFS(E:E,FILTER(B2:B10,(A2:A10=m)*(C2:C10="active")),F:F,"<>Off",G:G,"<="&TODAY()))))