Forum Discussion
TheOldPuterMan
May 10, 2020Brass Contributor
Filter function returning a "0" (Zero) when the source cell is blank or null
Hello all, I'm hoping that this isn't too obvious but I've noticed this situation several times and have had to work around it. I have a situation where the output of a FILTER function is cr...
ist_eco
Oct 19, 2020Copper Contributor
I faced the same problem a few weeks ago. Since I could not find a permanent solution, I used the following basic formula as a workaround.
=IF(SORT((FILTER(CHOOSE({1\2\3\4\5\6};Transactions!Provider;Transactions!Expense_Center; Transactions!Expense_Sub_Center;Transactions!Account;Transactions!Due_Date;Transactions!Budgeted_Amount);(Transactions!Provider<>"")*(Transactions!Expense_Center<>"")*(Transactions!Category="Expense")*(Transactions!Budgeted_Amount<>"");""));{1\2\3\4\5\6})=0;"";SORT((FILTER(CHOOSE({1\2\3\4\5\6};Transactions!Provider;Transactions!Expense_Center; Transactions!Expense_Sub_Center;Transactions!Account;Transactions!Due_Date;Transactions!Budgeted_Amount);(Transactions!Provider<>"")*(Transactions!Expense_Center<>"")*(Transactions!Category="Expense")*(Transactions!Budgeted_Amount<>"");""));{1\2\3\4\5\6}))
=IF(SORT((FILTER(CHOOSE({1\2\3\4\5\6};Transactions!Provider;Transactions!Expense_Center; Transactions!Expense_Sub_Center;Transactions!Account;Transactions!Due_Date;Transactions!Budgeted_Amount);(Transactions!Provider<>"")*(Transactions!Expense_Center<>"")*(Transactions!Category="Expense")*(Transactions!Budgeted_Amount<>"");""));{1\2\3\4\5\6})=0;"";SORT((FILTER(CHOOSE({1\2\3\4\5\6};Transactions!Provider;Transactions!Expense_Center; Transactions!Expense_Sub_Center;Transactions!Account;Transactions!Due_Date;Transactions!Budgeted_Amount);(Transactions!Provider<>"")*(Transactions!Expense_Center<>"")*(Transactions!Category="Expense")*(Transactions!Budgeted_Amount<>"");""));{1\2\3\4\5\6}))
- SergeiBaklanOct 19, 2020MVP
With IF() you return empty strings instead of zeroes, but that won't reduce the spill range. With that the option could be to apply custom number formatting which hides the zeroes.
IMHO, to exclude them at all it's better something like nested filter
=FILTER(FILTER(something),FILTER(something)<>0)
or even better with LET if it's avilable
=LET(myFilter, FILTER(something), Result, FILTER(myFilter, myFilter<>0), Result)
- NeimadTelMar 26, 2022Copper ContributorNested filter works nicely because it reduces the spill range and I use a data validation list. Thanks a lot
- SergeiBaklanMar 26, 2022MVP
NeimadTel , glad to help