Forum Discussion
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 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
I'll add another option to the mix which is pretty clean. If you only want text in the "spill" it removes the blank and "0".
=UNIQUE(FILTER(a1:a100,ISTEXT(a1:a100))
Just use this (&"") without brackets after any formula to get rid of 0 instead of blank cell. Subscribe Shakeel-IOSA YouTube Channel for more.
TheOldPuterMan I've had the same issue when just trying to return an array in another sheet i.e. =C2:C330. Simple solution is just put substitute around the whole formula =substitute(formula,"0","").
This works provided you don't want any cells with a 0 value i.e. text only arrays or values <>0.
Hope this helps
- By far the easiest solution is this:
=FILTER(range&"", condition)
Just add &"" after your range and the zeros are gone. No need for IFs, extra FILTERs or messing with conditional formatting.
Source:
https://stackoverflow.com/questions/64777658/excel-filter-returning-0-for-blank-cells- Excellent, worked like a charm for me and very easy to apply.
TheOldPuterMan The simple fix for me was unchecking the 'Show a zero in cells that have zero value'
Located here: File>Options>Advanced>Display options for this worksheet
On Mac it can be switched in the Properties (CMD-',') under "View -> Show in Workbook -> Zero Values"
- This is the real solution
Hi
I've been faced with the same problem.
To solve it, I enclose the FILTER function in a TEXT function.
=TEXT( FILTER( .... ), "" )
This keeps all texts in their normal format, but converts any number in an empty string.
Of course, il your source data contains numbers and you want to keep them, this is probably not the right solution.
Worked like a charm Sylvie_in_France
This was so close to solving my issue.
I have it pulling people and it was showing a 0 in the email address column if they didn't have an email address. Your TEXT() solution fixed that. Unfortunately I was also trying to pull in phone numbers.
EDIT: Now I am doing =IF(ISBLANK(FILTER(blahblahblah)),"",FILTER(blahblahblah))
I just used a similar approach, except that I also used LET to avoid having the repetition:
=LET(Grid,tblAttributes[#Data],Selected,tblAttributes[Selected],FILTER(IF(ISBLANK(Grid),"",Grid),Selected=TRUE))
I hope I'm understanding your question correctly. I'm new to excel but coming from google sheets.
However, I recently was tasked with creating materials lists that use the =FILTER function. If there is an empty cell in my =FILTER range, it returns a zero. I realized that excel is programmed to ignore the apostrophe ( ' ) symbol. I filled all the empty cells in my filter range with the apostrophes and now it presents a blank cell rather than a zero. in my test, SUMIFS skips over the apostrophe and continues to function properly.
Perhaps I misunderstood your question and suggested an inexperienced solution to something else but I hope this helps you or someone else.
Actually you added empty texts like ="" to such cells. SUM() and some other functions ignore any texts calculating results, thus cells with empty string (as well as cells with any other texts) don't affect totals calculated by SUM...().
Thus just take into account you are working with texts. If, for example, in A1 you add ', when =A1>25 returns TRUE since any text considered greater than any number. If A1 is blank, when =A1>25 returns FALSE since blank here is interpreted as zero.
There are many ways in Excel that lead to a result, here is a small suggestion.
Try conditional formatting
How about with
= and (a1 <> ""; a1> = 0)?
a1 is of course your field to be checked.I would be happy to know if I could help.
Nikolino
I know I don't know anything (Socrates)
* Kindly Mark and Vote this reply if it helps please, as it will be beneficial to more Community members reading here.
- 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}))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)
- Nested filter works nicely because it reduces the spill range and I use a data validation list. Thanks a lot
