COUNTIF beaviour with dynamic arrays

Hi, can someone pls explain to me why the following first example works but the second one does not?

There's apparently restrictions in what Countif (countifs etc.) are willing to accept as a range argument:

Works:
A1 = UNIQUE(B1:B10)
C1 = COUNTIF(A1#,"*")

Does not work (i.e. combination into one formula):
D1 = COUNTIF(UNIQUE(B1:B10),"*")

As a further question: Why does UNIQUE output an empty cell as a Zero?
What would be a way to have UNIQUE show both empty cells and cells with a true zero as distinct unique outputs?
What UNIQUE does instead is output two Zeroes as distinct unique items.

Any help much appreciated.

Thanks and Regards

Many thanks for the suggestion, Greg. This works, too.
I do not have to use Countif specifically.

After some more searching I did find an answer to my question.
It appears that Countif (and the same seems to apply to the entire family of *IFS-functions) simply wasn't programmed to handle array calculations in its arguments, whether it be the range or the criteria argument.
Additionally to your solution, this may be one of the few situations left where SUMPRODUCT might still be useful with dynamic arrays introduced now.

Cheers

Originally Posted by RaulSerg

It appears that Countif (and the same seems to apply to the entire family of *IFS-functions) simply wasn't programmed to handle array calculations in its arguments, whether it be the range or the criteria argument.

Not quite. The criteria argument can comprise array calculations of any type. The range argument can comprise a combination of one or more functions, provided, however, that this combination of functions resolves to an actual range reference.

Regards

Originally Posted by RaulSerg

=COUNTIF(B$2:INDEX(B$2:B$50,D$2,),">1") works just fine
while the same function with the range argument already resolved, say this being: {1;2;1;3;4}, that is:
=COUNTIF({1;2;1;3;4},">1") is something that does NOT work. (Again, SUMPRODUCT wouldn't have the same limitation in this particular instance.)

Good explanation.

Regards