Too many criteria in FILTER?

[dreid1011]

Am I trying to do too much with this FILTER formula?

=FILTER('Property Info'!A2:F8302,('Property Info'!E2:E8302="RESIDENCE")+('Property Info'!E2:E8302="MANF STRCT")+('Property Info'!E2:E8302="MULTI-FMLY")*('Property Info'!D2:D8302<>"110")*('Property Info'!D2:D8302<>"910")*('Property Info'!D2:D8302<>"915")*('Property Info'!D2:D8302<>"925")*('Property Info'!D2:D8302<>"935")*('Property Info'!D2:D8302<>"945")*('Property Info'!D2:D8302<>"955"))

WIth just the blue portion, I was getting the results I desired and column D was excluding values of 110, 910, 915, 925, 935, 945 & 955. After I added the orange portion, the results were no longer excluding those values. I did try swapping the + / * order, but that didn't help either.

 

[etaf]

how about adding brackets

=FILTER(A2:B6,((A2:A6="a")+(A2:A6="e"))*((B2:B6<>1)*(B2:B6<>3)))

(('Property Info'!E2:E8302="RESIDENCE")+('Property Info'!E2:E8302="MANF STRCT")+('Property Info'!E2:E8302="MULTI-FMLY"))
*
(('Property Info'!D2:D8302<>"110")*('Property Info'!D2:D8302<>"910")*('Property Info'!D2:D8302<>"915")*('Property Info'!D2:D8302<>"925")*('Property Info'!D2:D8302<>"935")*('Property Info'!D2:D8302<>"945")*('Property Info'!D2:D8302<>"955")))
so you have the ORs grouped and then the AND
as shown in this very simple example

Book1
	A	B	C	D	E	F
1
2	a	1			e	2
3	e	1			a	2
4	e	2
5	a	2
6	e	1
7	e	2
Sheet1

Cell Formulas
Range	Formula
E2:F3	E2	=FILTER(A2:B6,((A2:A6="a")+(A2:A6="e"))*((B2:B6<>1)*(B2:B6<>3)))
Dynamic array formulas.

 

[dreid1011]

how about adding brackets

=FILTER(A2:B6,((A2:A6="a")+(A2:A6="e"))*((B2:B6<>1)*(B2:B6<>3)))

(('Property Info'!E2:E8302="RESIDENCE")+('Property Info'!E2:E8302="MANF STRCT")+('Property Info'!E2:E8302="MULTI-FMLY"))
*
(('Property Info'!D2:D8302<>"110")*('Property Info'!D2:D8302<>"910")*('Property Info'!D2:D8302<>"915")*('Property Info'!D2:D8302<>"925")*('Property Info'!D2:D8302<>"935")*('Property Info'!D2:D8302<>"945")*('Property Info'!D2:D8302<>"955")))
so you have the ORs grouped and then the AND
as shown in this very simple example

Book1
	A	B	C	D	E	F
1
2	a	1			e	2
3	e	1			a	2
4	e	2
5	a	2
6	e	1
7	e	2
Sheet1

Cell Formulas
Range	Formula
E2:F3	E2	=FILTER(A2:B6,((A2:A6="a")+(A2:A6="e"))*((B2:B6<>1)*(B2:B6<>3)))
Dynamic array formulas.

Ah, yes that makes sense. Thank you.

 

[RoryA]

Just as an alternative, you can do something like this with MAP to create the criteria:

=FILTER('Property Info'!A2:F8302,MAP('Property Info'!E2:E8302,'Property Info'!D2:D8302,LAMBDA(e,d,AND(OR(e={"RESIDENCE","MANF STRCT","MULTI-FMLY"}),NOT(OR(d={"110","910","915","925","935","945","955"}))))))