Filter formula with more than 1 criteria

[floggingmolly]

I'm trying to use a filter formula using 1 of 2 criteria, or both depending on what the user selects. In cell B2 they can select a district, and in H2 then can select a station. I want the data to filter depending on what they choose. It's working if they select 1 criteria, for example if they choose a district in B2 then the data filters to that district. If they choose a Station in H2 the data filters to that station. But if they choose a district and a station, it only filters the district and still shows all stations for that district. Is there a way to make it filter by both criteria if they are both entered? I've tried for 2 days but can't get it. Any help would be appreciated. I'm not even sure if the Filter function can do this but thought I'd give it a shot. Here is the formula I'm using: =FILTER(ALL_DATA,((ALL_DATA[District]=B2) * (ALL_DATA[Station]=H2)) + ((ALL_DATA[District]=B2) + (ALL_DATA[Station]=H2)),"")

 

[Fluff]

How about

=FILTER(ALL_DATA,(IF(B2="",1,ALL_DATA[District]=B2))*(IF(H2="",1,ALL_DATA[Station]=H2)))

 

[floggingmolly]

How about

=FILTER(ALL_DATA,(IF(B2="",1,ALL_DATA[District]=B2))*(IF(H2="",1,ALL_DATA[Station]=H2)))

I think this will work. Is there a way to hide the #VALUE! error if both cells are blank? I tried using the ,"" but the error is still visible.

 

[Fluff]

How about

=IF(AND(B2="",H2=""),"",FILTER(All_Data,(IF(B2="",1,All_Data[District]=B2))*(IF(H2="",1,All_Data[Station]=H2))))

 

[floggingmolly]

I think this will work. Is there a way to hide the #VALUE! error if both cells are blank? I tried using the ,"" but the error is still visible.

I got it, using IFERROR. The formula you provided works perfect. Thank you so much for your help and the quick response. Much appreciated. Have a great day.

 

[Fluff]

You're welcome & thanks for the feedback.

 

[floggingmolly]

You're welcome & thanks for the feedback.

Could you explain the "1" in the formula? I've been using Excel for quite a while but have never seen this. I would like to know how this works.

 

[Fluff]

In this instance, It just signifies True.

 

[floggingmolly]

In this instance, It just signifies True.

Ok, got it. Thank you

 

[Fluff]

You're welcome.