Google Sheet: Countif/Filter working with isblank-CodePudding

I am trying to count non-blank cells in a column, here is what I do

COUNTIF(D:D,NOT(ISBLANK(D:D)))

It doesn't work and always returns "0". I am aware there are other better ways like COUNTA and =countif(D:D,"<>"), but for learning purpose, I am wondering how I can do this with isblank. I think the problem here is countif is running on a range while isblank is running on a cell. However, this works and it puzzles me:

FILTER(D:D,NOT(ISBLANK(D:D)))

There seems to be a type of function that is designated to be working on arrays. I think it plays a role in this situation. On Google sheet's document page like https://support.google.com/docs/answer/3093197?hl=en it doesn't mention whether a function is "Array function" or not, so how do I know when I can put functions like isblank in a parameter of another function and it would loop through the range defined by its own parameter? In other words, why isblank is able to loop through D:D when it is in FILTER function but not in COUNTIF? What is the difference between FILTER and COUNTIF here and where is the document referencing this difference? Is there any other function like FILTER that would work with functions like isblank?

CodePudding user response：

while there are few major differences between FILTER and COUNTIF (like that FILTER is ARRAYFORMULA type of function while COUNTIF is something in between) the issue here is that syntax-wise they are both different same as this wont work:

=COUNTIF(D:D, NOT(ISBLANK(D:D)))

this will not work eiter:

=FILTER(D:D, "<>")

also if you take a look at ISBLANK this function needs to be arrayed (be part of FILTER, INDEX or ARRAYFORMULA in cases like yours when you work with arrays) like:

=INDEX(NOT(ISBLANK(D:D)))

otherwise only the first cell would be considered. meanwhile, if you wrap COUNTIF into FILTER, INDEX or ARRAYFORMULA it will process value for each row eg. it will act in a different way than expected. also to see if the function is supported under ARRAYFORMULA or not you can just simply test it like:

=ARRAYFORMULA(IS_BLANK(D:D))