I am writing a spill formula that I would like to filter a range by department and/or name, and if no selections are made spill the entire range.
Currently, I have it written where it is able to filter one or the other or both; however, I get a #Value! error if they’re both blank.
=FILTER(A2:C6, IF( ISBLANK(A8),1,(A2:A6=A8) ) * IF( ISBLANK(B8),1,(B2:B6=B8) ) ,"")
| Dept | Person | Value |
|---|---|---|
| Sales | Mark | 100 |
| Sales | Steve | 100 |
| Sales | Casey | 100 |
| MGMT | Michelle | 100 |
| MGMT | John | 100 |
(Image to follow the formula, markdown table for testing)
>Solution :
One can simply add the condition when blank to the areas
=FILTER(A2:C6,((A2:A6=A8)+(A8="")>0)*((B2:B6=B8)+(B8="")>0))
