Forum Discussion
Filter with Multiple Criteria
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.
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) )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. 🙂
OMG, I cannot thank you enough! It worked! You really saved me. THANK YOU!!
