Filter with Multiple Criteria

[LuluLehman]

My spreadsheet filters contacts to those meeting 9 criteria.  Not all criteria need to be met.  My filter includes each criteria (using "*").  However, many cells in the data file are blank, which are showing as "0" in my filtered spreadsheet.  When the criteria is left blank, it filters to those that are blank.  I prefer if blank, the criteria includes any data within the list for that cell and blank cells.  Is there a function to use to filter on the criteria ONLY if there is a selection from the list in the criteria cell?

Here is my formula.  It pulls from the data file (Database) tab and filters to the Pull Data tab.

=FILTER(Database!A2:BH366,(Database!H2:H366='Pull Data'!O2)*(Database!U2:U366='Pull Data'!P2)*(Database!V2:V366='Pull Data'!Q2)*(Database!X2:X366='Pull Data'!R2)*(Database!Z2:Z366='Pull Data'!S2)*(Database!AA2:AA366='Pull Data'!T2)*(Database!AB2:AB366='Pull Data'!U2)*(Database!AK2:AK366='Pull Data'!V2)*(Database!R2:R366='Pull Data'!W2),"none")

I greatly appreciate your help in advance.

[#2]

@LuluLehman 

Try this formula change.

=FILTER(Database!A2:BH366,
(IF('Pull Data'!O2<>"", Database!H2:H366='Pull Data'!O2, TRUE))*
(IF('Pull Data'!P2<>"", Database!U2:U366='Pull Data'!P2, TRUE))*
(IF('Pull Data'!Q2<>"", Database!V2:V366='Pull Data'!Q2, TRUE))*
(IF('Pull Data'!R2<>"", Database!X2:X366='Pull Data'!R2, TRUE))*
(IF('Pull Data'!S2<>"", Database!Z2:Z366='Pull Data'!S2, TRUE))*
(IF('Pull Data'!T2<>"", Database!AA2:AA366='Pull Data'!T2, TRUE))*
(IF('Pull Data'!U2<>"", Database!AB2:AB366='Pull Data'!U2, TRUE))*
(IF('Pull Data'!V2<>"", Database!AK2:AK366='Pull Data'!V2, TRUE))*
(IF('Pull Data'!W2<>"", Database!R2:R366='Pull Data'!W2, TRUE)),
"none")

. I don't have a Mac myself to try it out, but it should work in Mac too...although I'm not so sure. 🙂

[#3]

@NikolinoDE 

OMG, I cannot thank you enough!  It worked!  You really saved me.  THANK YOU!!

[#4]

yw

[#5]

@LuluLehman 

Here's an example where there's 3 criteria:

=LET(
    k, COLUMNS(crit),
    arr, SEQUENCE(k, , 1, 0),
    FILTER(data, MMULT((crit = input) * 1, arr) = k)
)