Countif with literal array

Question

Why does this formula not work: COUNTIF({1,2,3},2)?

I can use both COUNT() and COUNTIF() with a range, but I can replace the range with a literal array only on COUNT, not on COUNTIF.

If I use LET to first assign the array to a name, then I can use a literal array in COUNTIF, but i get the wrong result.

This is the content of a little test that I am using:

	A	Notes
1	1
2	2
3	3
4
5	=COUNT(A1:A3)
6	=COUNTIF(A1:A3,2)
7	=COUNT({1,2,3})
8	=COUNTIF({1,2,3},2)	Excel doesn't allow to enter this formula
9
10	=LET(a,A1:A3,COUNT(a))
11	=LET(a,A1:A3,COUNTIF(a,2))
12	=LET(a,{1,2,3},COUNT(a))
13	=LET(a,{1,2,3},COUNTIF(a,2))	Excel allows this, but the result is wrong

The following snapshot shows the formulas I'm trying to use:

The following snapshot shows the results:

The following snapshot shows the error message when I try to enter the formula in A8:

Answer 1

You can't use arrays in place of ranges with any of the *IF(S) formulas. They require ranges.

One can use SUMPRODUCT to do most:

=SUMPRODUCT(--({1,2,3}=2))

This will return an array: {0,1,0} to the SUMPRODUCT which will then sum them and return 1