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 cr...
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.
