Using Filter Formula with Multiple Criteria and Allowing Blank Criteria

1
votes

Below is a link to a file with fake data. The 2nd tab is designed to allow users to filter the larger data set by selecting criteria from the drop-down. How do I design a formula (currently located in cell B12) so that it filters the larger data set from Sheet1, but if the user leaves "Activity" blank, it returns all results? Currently, the formula is requiring a selection in every dropdown. I want it so that if someone does NOT select something in a dropdown, that it will return all results based on what criteria have been entered.

Dummy data

1
if-statementfiltergoogle-sheetsgoogle-sheets-formula

1 Answers

0
votes
=FILTER(Sheet1!$A$2:$F,
 IF(C2<>"", Sheet1!$A$2:$A>=$C$2, Sheet1!$A$2:$A<>""),
 IF(F2<>"", Sheet1!$A$2:$A<=$F$2, Sheet1!$A$2:$A<>""),
 IF(C4<>"", Sheet1!$B$2:$B =$C$4, Sheet1!$B$2:$B<>""),
 IF(F4<>"", Sheet1!$C$2:$C =$F$4, Sheet1!$C$2:$C<>""))

0