If i do countA on a columnA of sheet1 it says 899 results. If on sheet2 I do either of =FILTER('sheet1'!A6:A5826,'sheet1'!A6:A5826<>"") or =FILTER('sheet1'!A6:A5826,(NOT(ISBLANK('sheet1'!A6:A5826))))
then i only get 876 rows. wny?
countA is correct because when I copy my original column A from sheet1 and paste it next to the result of the two FILTERED columns in sheet2 I can see an additional 23 rows.
and if i sort these columns so i can identify the problem cells, I find relatively innocuous cells with text strings in the original data, results which are completely missing in my two FILTERED attempts.
Rather than trying to understand the wizardry behind this outcome, can anyone offer me a more watertight way to do the FILTER so that I get 899 results? I have tried various gleanings from the web but not found something that works eg =SUMPRODUCT(–(LEN(bj6:bj5826)>0)) which i stumbled upon somewhere.
Appreciate any (simple-ish) suggestions
When you do:
=FILTER(A:A,A:A<>"")
or
=FILTER(A:A,LEN(A:A))
The formula filters out both empty cells and cells that contain an empty string "", so the empty strings are not taken into account.
But when you do:
=FILTER(A:A,NOT(ISBLANK(A:A)))
The formula only filters out the cells that are actually empty leaving the cells that contain the empty string "".
Since the COUNTA function also counts empty strings "", these two formulas are equivalent:
=COUNTA(A:A)
=ROWS(FILTER(A:A,NOT(ISBLANK(A:A))))
While these two aren't:
=COUNTA(A:A)
=ROWS(FILTER(A:A,A:A<>""))
This is why returning an empty string is considered bad practice, and it’s always better to avoid it.
❌ IF(condition,"",do_something)
✅ IF(condition,,do_something)
❌ IFERROR(formula,"")
✅ IFERROR(formula)