CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formulaformula
Tim B
Tim B

Reputation: 319

How to use IF within FILTER for multiple criteria

I'm trying to filter a range on one or multiple criteria. I have two cells where I can enter one or more criteria. The filtering works when a value is entered into all of the criteria cells, but if I leave one of them blank and only want to filter on just one criteria then it gives an error.

=FILTER(RANGE, if(CRITERIA1<>"",COL1=CRITERIA1) ,REGEXMATCH(COL2, CRITERIA2))

I want to add more criteria as the range has more columns, but I want to get it working with just two criteria for now. How can I get this to work?

Upvotes: 0

Views: 71

Answers (1)

player0
player0

Reputation: 1

use query:

=QUERY(A:B, "where 1=1 "&
 IF(A1="",," and Col1 = '"&A1&"'")
 IF(B1="",," and Col2 = '"&B1&"'"), 0)

Upvotes: 1

Related Questions