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)
Online has a lot of solutions regarding two filters at once and ignoring one if blank, but this problem required being able to ignore the filter entirely if all conditions are blank.
The solution I was able to determine was I could simply refer to the entire range as a formula input to spill it un-edited. Combining this with wrapping the entire formula in an IF statements gave me my desired results.
=IF( AND(M8="",N8=""),M2:O6, FILTER(M2:O6, IF( ISBLANK(M8),1,(M2:M6=M8) ) * IF( ISBLANK(N8),1,(N2:N6=N8) ),""))
Another way I was able to successfully get my desired results was by breaking this down into a Let() formula:
=LET(raw,M2:O6,filtered,FILTER(M2:O6,IF(ISBLANK(M8),1,(M2:M6=M8))*IF(ISBLANK(N8),1,(N2:N6=N8))), IF(AND(M8="",N8=""), raw,filtered))
Using the first Formula:
Using the Second Formula:
