Is there any real reason why , when we are dealing with arrays , SUMPRODUCT will be faster than SUM entered using CTRL SHIFT ENTER ?
I doubt it. And I agree with you that the issue is largely one of perception re the two forms.
Basically, almost almost every function can be made to operate over
either a single value
or an array of values. Usually, the native form of these functions is that which operates over a single value; to coerce operation over an array uses the same function though entered into the worksheet in a different manner (with CTRL+SHIFT+ENTER).
Microsoft could equally have decided, just as I understand is the case with Google Sheets, to make it so that there exist two
separate functions for this purpose, e.g. SUM, ARRAYSUM, etc.
However, there are some native functions which are such that operating over a single value - or single range - would make very little sense. In these cases Microsoft evidently decided that it would be more useful to make it so that the native form of these functions was the one which operated over an array, thus removing the need for the dual CSE/Non-CSE set-up existing with the vast majority of other functions.
MMULT is such an example: given that this function is in any case designed to take the (matrix) product of two arrays, a "non-array" version which operated over a single input would be redundant.
Perhaps Microsoft introduced SUMPRODUCT along lines of similar thought, i.e. that because the array version of SUM was so widely-used they thought it would be of benefit to users to have this functionality without the need for the pressing of a few extra keys. I don't know to be sure. But, whatever the reason, I agree with you that I cannot see why there should be any difference between the performance of the two versions.
Regards
P.S. I should clarify my statement re LOOKUP, INDEX and AGGREGATE, since it is not in
every use that I am claiming that these functions are operating as array formulas.
These are
not, as far as I'm concerned, array formulas:
=AGGREGATE(14,6,A1:A10,1)
=INDEX(A1:A10,,0)
=LOOKUP(10,A1:A10)
But these
are:
=AGGREGATE(14,6,A1:A10/(B1:B10="B"),1)
=MAX(INDEX((B1:B10="B")*A1:A10,,0))
=LOOKUP(1,0/(B1:B10="B"),A1:A10)