05-10-2020 11:56 AM
05-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
10-19-2020 01:39 AM
10-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.
10-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)
11-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.
11-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.