Filter Function - Ignore Criteria when blank

[Glasgowsmile]

Hello,

I've got a filter formula that I'm using I noticed if any of the cells are blank (like L1, M1, N1, or O1) then I get a #calc error. Is there a way to ignore those criteria when the reference cells are blank?

=FILTER(A2:H412,(F2:F412=K1)*(H2:H412=L1)*(C2:C412=M1)*(A2:A412>=N1)*(A2:A412<=O1),)

 

[Takae]

Please put "" at last.


=FILTER(A2:H412,(F2:F412=K1)*(H2:H412=L1)*(C2:C412=M1)*(A2:A412>=N1)*(A2:A412<=O1),"")

 

[KRice]

One work-around is to place each of the array match expressions inside an IF statement and return 1 for any of the expressions where the K:O inputs are blank:

=FILTER(A2:H412,IF(K1="",1,(F2:F412=K1))*IF(L1="",1,(H2:H412=L1))*IF(M1="",1,(C2:C412=M1))*IF(N1="",1,(A2:A412>=N1))*IF(O1="",1,(A2:A412<=O1)),"")

 

[Glasgowsmile]

One work-around is to place each of the array match expressions inside an IF statement and return 1 for any of the expressions where the K:O inputs are blank:

=FILTER(A2:H412,IF(K1="",1,(F2:F412=K1))*IF(L1="",1,(H2:H412=L1))*IF(M1="",1,(C2:C412=M1))*IF(N1="",1,(A2:A412>=N1))*IF(O1="",1,(A2:A412<=O1)),"")

Thank you, this saved me a tremendous amount of time instead of doing IF statements for every possible combination.

 

[KRice]

I'm happy to help.