Filter function returning a "0" (Zero) when the source cell is blank or null

Brass Contributor

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 creating a "0" (Zero) when the input cell is blank. I' tested for possible spaces or a real zero in the source and come up dry. The source really is a null.


The end result is when I go to compare the contents of the output cell in a SUMIFS there is a no-match situation and the values I need are not added into the sum. 


As long as the source cell has anything in it, other than a null, all is good. 


I've seen this before but have yet to find an answer.


This is the formula I'm using to create the data I need. The Cell in question is Transactions!Expense_Sub_Center. When if is Null or empty I get a zero-entry in the output cell.


=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})


I've attached a copy of the file. The source sheet is "Transactions" and the output sheet is "Budget Table".


Thanks all,


TheOldPuterMan AKA John

22 Replies



Unfortunately this converts everything to text. 

Excellent, worked like a charm for me and very easy to apply.