FILTER Formula: Simultaneous AND and OR Conditions?

[engrnoir]

Is it possible to make my conditions in Filter Formula with both AND and OR (the condition for OR is multiple and dynamic)?
For example, I have a table:

Description	Year	Month	Type
Item 1	2023	Mar	Fruit
Item 2	2024	Mar	Fruit
Item 3	2025	Mar	Fruit
Item 4	2024	Mar	Vegetable
Item 5	2023	Jan	Fruit
Item 6	2024	Jan	Fruit
Item 7	2025	Jan	Fruit
Item 8	2024	Jan	Vegetable

Now in this data sample, I'm trying to use the Filter Formula where I can include items using both AND conditions and OR conditions.
I know how to use Multiple AND conditions [(X="value")*(Y="value2")*...], and OR conditions [(X = "value1")+(Y = "value 2") + ...], I just haven't had the opportunity to use both at the same time.

In this example, let's say I want to filter out all fruits, but I want to filter out all fruits in varying Year and Month (my data set will possibly have same type but will have singular or multiple months/years). My reference for the varying part is a text string of years/months separated by a comma (",") (i.e., 2023,2024,2025...; Jan,Feb,Mar...).
Input Values:
Sample 1:
Year: 2023,2024,2025
Type: Fruit
Month: Jan
Result should be Item 1, Item 2, and Item 3. The OR part comes in at the Year condition, while the AND part comes in at the Month (Mar) and Type (Fruit).

Sample 2:
Year: 2023
Type: Fruit
Month: Jan,Mar
Result should be Item 1, and Item 5. The OR part comes in at the Month (January OR March), while the AND part comes in at the Year (2023) and Type (Fruit).

I apologize in advance if I'm overthinking this or something along those lines, or if I didn't make my question clear. This is the first forum I've ever been in and I already appreciate everything I have seen here before. LOL

 

[Anonymous1378]

As in [ISNUMBER(XMATCH(X,values_in_an_array))*...] or [COUNTIF(values_in_a_range,X)*...]?

 

[Alex Blakenburg]

I'm sure someone can come up with a better way but for Sample 1 this should work.
Sample 2 is just a variation of this.

=LET(rYr,$B$2:$B$9,
FILTER($A$2:$D$9,((rYr=2023)+(rYr=2024)+(rYr=2025))*($D$2:$D$9="Fruit")*($C$2:$C$9="Jan"),""))

 

[kevin9999]

I don't think your expected result is correct for Sample 1 (you want Jan but items 1, 2 and 3 are Mar) so see if this is what you're looking for.

Book1
	A	B	C	D
1	Description	Year	Month	Type
2	Item 1	2023	Mar	Fruit
3	Item 2	2024	Mar	Fruit
4	Item 3	2025	Mar	Fruit
5	Item 4	2024	Mar	Vegetable
6	Item 5	2023	Jan	Fruit
7	Item 6	2024	Jan	Fruit
8	Item 7	2025	Jan	Fruit
9	Item 8	2024	Jan	Vegetable
10
11	Item 5	2023	Jan	Fruit
12	Item 6	2024	Jan	Fruit
13	Item 7	2025	Jan	Fruit
14
15	Item 1	2023	Mar	Fruit
16	Item 5	2023	Jan	Fruit
17
Sheet1

Cell Formulas
Range	Formula
A11:D13	A11	=FILTER(A2:D9,((B2:B9=2023)+(B2:B9=2024)+(B2:B9=2025))*(C2:C9="Jan")*(D2:D9="Fruit"),"")
A15:D16	A15	=FILTER(A2:D9,(B2:B9=2023)*(D2:D9="Fruit")*((C2:C9="Jan")+(C2:C9="Mar")),"")
Dynamic array formulas.

 

[RoryA]

For 365 you could use MAP but that wouldn't work on 2021 which is also in your profile. Does it have to work in both?

 

[engrnoir]

Apologies for the late reply. Weekend came and I already was out of office by the time someone replied.

Mr. Alex Blakenburg's approach is kind of what I thought at first (practically similar to kevin9999's approach as well). However, I was actually thinking of recreating this part dynamically instead of a direct input:
...((B2:B9=2023)+(B2:B9=2024)+(B2:B9=2025))... (this one was taken from kevin9999's approach).

That instead of a direct input of 2023, 2024, and 2025, it kind of summarizes that any inputs from my specific text string (i.e., "2023,2024,2025" which will be separated by a comma using TEXTSPLIT, or "2023,2024", "2023,2024,2025,2026", and so on) shall be considered in the condition. The year condition (and month) is going to both be dynamic in this setting.

For Mr. RoryA and Anonymous1378, I have yet to try your insights (I just got back to the office). I probably might take longer with the MAP formula though since I am not yet familiar with that (I have used it before, but I just do not understand it lol). I am currently using Office 365 (I had a 2021 version I think when I created this profile so no issues there).

 

[Peter_SSs]

I am currently using Office 365 (I had a 2021 version I think when I created this profile so no issues there).

So, if you do not have 2021 version now, wouldn't it be a good idea to remove it from your profile (click your user name at the top right of the forum, then ‘Account details’ - don’t forget to scroll down & ‘Save’) so helpers know what resources you do have available?

 

[Alex Blakenburg]

That instead of a direct input of 2023, 2024, and 2025, it kind of summarizes that any inputs from my specific text string (i.e., "2023,2024,2025" which will be separated by a comma using TEXTSPLIT, or "2023,2024", "2023,2024,2025,2026", and so on) shall be considered in the condition. The year condition (and month) is going to both be dynamic in this setting.

I am currently using Office 365

That leads me to something like this:

20240105 Filter And Or engrnoir.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Description	Year	Month	Type		Input Parameters			Result
2	Item 1	2023	Mar	Fruit
3	Item 2	2024	Mar	Fruit		Year:	2023,2024,2025		Description	Year	Month	Type
4	Item 3	2025	Mar	Fruit		Type:	Fruit,Vegetable		Item 1	2023	Mar	Fruit
5	Item 4	2024	Mar	Vegetable		Month:	Jan,Mar		Item 2	2024	Mar	Fruit
6	Item 5	2023	Apr	Fruit					Item 3	2025	Mar	Fruit
7	Item 6	2024	Jan	Bread					Item 4	2024	Mar	Vegetable
8	Item 7	2025	Jan	Fruit					Item 7	2025	Jan	Fruit
9	Item 8	2024	Jan	Vegetable					Item 8	2024	Jan	Vegetable
10
11
Sheet1 (2)

Cell Formulas
Range	Formula
I4:L9	I4	=LET( arrYr,BYROW(IF($B$2:$B$9=VALUE(TEXTSPLIT(G3,",")),1,0),LAMBDA(YrMatch,SUM(YrMatch))), arrType,BYROW(IF($D$2:$D$9=TEXTSPLIT(G4,","),1,0),LAMBDA(TypeMatch,SUM(TypeMatch))), arrMth,BYROW(IF($C$2:$C$9=TEXTSPLIT(G5,","),1,0),LAMBDA(MthMatch,SUM(MthMatch))), FILTER($A$2:$D$9,arrYr*arrType*arrMth,""))
Dynamic array formulas.

 

[engrnoir]

Okay, so I just tried Mr. Alex Blakenburg's new approach and this one worked as I intended. This actually helps me more than I intended. Thanks!

 

[RoryA]

FWIW, the MAP equivalent:

=FILTER($A$2:$D$9,MAP(B2:B9,C2:C9,D2:D9,LAMBDA(yr,mnth,frt,AND(OR(yr=VALUE(TEXTSPLIT(G3,","))),OR(mnth=TEXTSPLIT(G5,",")),OR(frt=TEXTSPLIT(G4,",")),""))))