Why doesn't COUNTIFS work with array constants in the formula, when it works with references with the spilled range operator?

Question

1. If ={1;1;2} is entered into A1 and ={1;2} into B1, this formula =COUNTIFS(A1#, B1#) returns an array of the same dimension as the criteria range (the value {2;1}).
2. But if the array constants are entered directly into the formula, there is an error message that there is a problem with the formula syntax: =COUNTIFS({1;1;2}, {1;2})
3. This formula returns an array with the dimension of range of #VALUE! errors: =LET(range, {1;1;2}, criteria, {1;2}, COUNTIFS(range, criteria)) (returns the value {#VALUE!;#VALUE!;#VALUE!})
4. And this formula returns a #CALC! error: =LET(range, {1;1;2}, criteria, {1;2}, MAP(criteria; LAMBDA(x; COUNTIFS(range, x)))

Why doesn't 2, 3, or 4 work when 1 does?

Answer 1

@Sigve Berge Hofland

The first argument in COUNTIF must be a regular or spilled worksheet range. A1:A3 or A1# will both work.

But an array constant like {1;2;3} returns an 'in memory array' rather then a worksheet range (regular or spilled).

Edit: No intent to copy Rory's answer. Didn't see it until after I posted mine.

Answer 2

It's how they were programmed. All of the xxxIF/xxxIFS functions require actual worksheet ranges for everything except the criteria. That's also why they don't work when the source file(s) are not open.