We will pick this apart loosely, I'm using INCITS+ISO+IEC+14882-2003. I'll quote the small stuff, but some of the more complex stuff is too large to quote.
sizeof is defined in §5.3.3, and it says (abridged):
The sizeof operator yields the number of bytes in the object representation of its operand. The operand is either an expression, which is not evaluated, or a parenthesized type-id.
In other words, it yields the size of a type in bytes or finds the type of an expression and yields the size of that. We don't have a type, we have the expression arrayCountofHelper(arr).
You can dissect this expression by looking at the definitions of primary-expression and postfix-expression as defined in §5.1 and §5.2 respectively. You will find it is a postfix-expression and fits the requirements of a function call (§5.2.2).
Now back to sizeof. We only care about the type of this function call expression (so we can yield the size of it), and §5.2.2/3 says:
The type of the function call expression is the return type of the statically chosen function [...]. This type shall be a complete object type, a reference type or the type void.
So we need to find the return type of the function that would be called (remember, this is all unevaluated) with arrayCountofHelper(arr). arrayCountofHelper is a function template which we would be instantiating (§14.7), so we need to do that before we have an instantiated function to get the return type of.
All the template parameters need to have values (§14.8.2), and by using the rules defined in §14.8.2.1 we will find T and N by matching the array passed to the function with the functions parameter (which is a reference to an array). (For example, if arr was int[10], T would be int and N would be 10.) Once we have those, the function can be instantiated.
Once instantiated, the return type of the function would be char(&)[N]*, a reference to an array of N char's. (If you need help parsing, see §8.3.5. There are also questions on SO on how to parse "complex" types.) So now we've found the type of the expression, we must take the size of it.
§5.3.3/2 defines how sizeof works with references and arrays (emphasis mine):
When applied to a reference or a reference type, the result is the size of the referenced type. When applied to a class, the result is the number of bytes in an object of that class including any padding required for placing objects of that type in an array. The size of a most derived class shall be greater than zero (1.8). The result of applying sizeof to a base class subobject is the size of the base class type.70) When applied to an array, the result is the total number of bytes in the array. This implies that the size of an array of n elements is n times the size of an element.
The size of a reference type is the size of its referenced type, so we need to size of char[N]. The size of this is N * sizeof(char). char is fundamental as it is the smallest type there is; that is, sizeof(char) is always one. (§5.3.3/1) So the size yielded by this expression is 1 * N, or what we wanted the whole time: N.
And that's how it works.
The reason this one is preferred over your last example of arrayCountof is because the result of sizeof is a constant expression, so can be used in places where a constant expression is required.
It should be noted that in C++0x, we can get our clean no-macro syntax with:
template <typename T, unsigned N>
constexpr size_t arrayCountof(T(&)[N]) {return N;}
* The reason the function return type is a reference to an array and not an array is simply because you cannot return arrays. If you could, either choice would suffice.