1

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?

player0
  • 124,011
  • 12
  • 67
  • 124
shenkwen
  • 3,536
  • 5
  • 45
  • 85

1 Answers1

2

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))
player0
  • 124,011
  • 12
  • 67
  • 124
  • Thanks. However your answer brings up more questions. Let me start with this: you mention "be part of FILTER, INDEX or ARRAYFORMULA", I noticed you didn't use "etc", so do you mean these 3 functions are of the same `ARRAYFORMULA` type? If not, where can I see a list of `ARRAYFORMULA` type functions? – shenkwen Sep 22 '21 at 15:57
  • 1
    @shenkwen yes. there are no more only those 3. meaning there are more of them (every function that is able to output array is arraytype fx like SORT, GOOGLEFINANCE, etc.) but only these 3 are "truly" arraytypes - eg. they are able to expand regular non-array functions like MATCH, COUNTIF, REGEXEXTRACT, SUBSTITUTE, etc. into arrays simply by wrapping them. so if for example you got simple FILTER(A:A, LEN(B:B)>5) you dont need to wrap it into ARRAYFORMULA coz FILTER will support LEN to account for whole array. also you can use INDEX instead of ARRAYFORMULA just coz its shorter. – player0 Sep 22 '21 at 23:03
  • 1
    @shenkwen sadly there is no official documentation of which formula is supported under ARRAYFORMULA or which formula acts as array. its a total mess. for example, ISDATE is not supported under ARRAYFORMULA but if you use ISDATE_STRICT then this one supports arrays, but the issue is that this one is an undocumented secret function.yup, thats a real deal. there are hidden functions with no documentation like FLATTEN was until recently after MK discovered it and we started using it like everywhere. another example could be function WHATTHEFOXSAY https://stackoverflow.com/a/66201717/5632629 – player0 Sep 22 '21 at 23:17