May 10 2020 11:56 AM
May 10 2020 11:56 AM
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.
I've attached a copy of the file. The source sheet is "Transactions" and the output sheet is "Budget Table".
TheOldPuterMan AKA John
Oct 19 2020 01:39 AM
Oct 19 2020 02:31 AM
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.
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.
Oct 19 2020 09:50 AM
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
or even better with LET if it's avilable
=LET(myFilter, FILTER(something), Result, FILTER(myFilter, myFilter<>0), Result)
Nov 24 2020 10:59 AM
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.
Nov 24 2020 11:40 AM
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.
Feb 19 2021 07:24 AM
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.
May 28 2021 09:20 AM - edited May 28 2021 09:27 AM
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))
Jun 04 2021 10:27 AM
@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
Jun 08 2021 04:15 AM
@RC2pc the ISBlank worked for me thanks
Mar 26 2022 05:28 AM
Jul 25 2022 12:00 PM
I just used a similar approach, except that I also used LET to avoid having the repetition:
Oct 26 2022 01:33 AM
On Mac it can be switched in the Properties (CMD-',') under "View -> Show in Workbook -> Zero Values"
Feb 03 2023 03:09 AM
Feb 21 2023 02:31 AM
@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
Mar 08 2023 01:58 AM
Just use this (&"") without brackets after any formula to get rid of 0 instead of blank cell. Subscribe Shakeel-IOSA YouTube Channel for more.
Mar 22 2023 06:16 AM
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".