Excel FILTER() returning 0 for blank cells

Question

I suspect this has been asked previously, but I can't find it.

FILTER() will often return a 0 for blank rows, even when a return string is specified.

Using filter() I am often getting a 0 return value for empty cells. Assume these 6 rows of data in column A:

abc
xyz
abc

xyz
abc

If I use

FILTER(A10:A15, A10:A15 <> "xyz", "")

I get back the following (sometimes):

abc
abc
  0
abc

This seems to be somewhat unpredictable. Having 0s show up where I don't want is a problem and requires extra logic or filtering. Is this a known issue? Other than explicitly filtering out blank cells, is there another way to solve this problem?

This seems to work to get rid of 0 returning for empty cells:

FILTER(A10:A15, (A10:A15 <> "xyz") * (A10:A15 <> ""), "")

This is returned:

abc
abc
abc

I can live with this solution but it should be unnecessary. I also end up explaining why I'm filtering empty cells to people over and over.

btw, filtering out 0 doesn't work. FILTER() seems to see the cell as a blank string when reading it, but not when outputting the result.

FILTER(A10:A15, (A10:A15 <> "xyz") * (A10:A15 <> 0), "")

This will return the original results with the 0.

If there is no better solution, is there an explanation?

Answer 1

As explained here, the third argument (If_empty) is not for cases when there is a blank.

FILTER(array, include, [if_empty])

Where:
Array (required) - the range or array of values that you want to filter.
Include (required) - the criteria supplied as a Boolean array (TRUE and FALSE values). Its height (when data is in columns) or width (when data is in rows) must be equal to that of the array argument.
If_empty (optional) - the value to return when no entries meet the criteria.

Your function would return the string specified in If_empty if all the values in Array were "xyz". I.e., the criteria in Include would not evaluate to TRUE for any non-blank value in the range Array.

Also, it seems that your last formula does not anymore return zeros: