Code efficiency of Sumproduct()

J

Jack Schitt

Two formulae generating the same result:
{=SUM(array1*array2*...arrayn)}
=SUMPRODUCT(array1*array2*...arrayn)

Does the non-array version use up fewer PC resources and/or calculate faster
than the array version?
Or do they both compile the same way?
 
F

Frank Kabel

Hi
use
=SUMPRODUCT(array1,array2,...,arrayn)

This should be faster than the other two versions
 
A

Aladin Akyurek

Jack,

If you invoke SumProduct using its native comma syntax, the answer is yes,in
particular when the arrays are numerical.. With the "* syntax", they are
equivalent. Invoking Sumproduct with the comma syntax requires coercing if
array arguments are conditionals evaluating to arrays of logical values as
in:

=SUMPRODUCT((A1:A10="x")+0,(B1:B10="y")+0)

=SUMPRODUCT(--(A1:A10="x"),--(B1:B10="y"))

BTW, invoking SUM with multiple ranges evaluating to either numerical or
logical arrays, the * operator takes care of the coercion where needed.

See for more:

http://tinyurl.com/2obhh
 
J

Jack Schitt

Thanks to all 3. Much appreciated as always.

Aladin Akyurek said:
Jack,

If you invoke SumProduct using its native comma syntax, the answer is yes,in
particular when the arrays are numerical.. With the "* syntax", they are
equivalent. Invoking Sumproduct with the comma syntax requires coercing if
array arguments are conditionals evaluating to arrays of logical values as
in:

=SUMPRODUCT((A1:A10="x")+0,(B1:B10="y")+0)

=SUMPRODUCT(--(A1:A10="x"),--(B1:B10="y"))

BTW, invoking SUM with multiple ranges evaluating to either numerical or
logical arrays, the * operator takes care of the coercion where needed.

See for more:

http://tinyurl.com/2obhh
Click to expand...
 
Top