Code efficiency of Sumproduct()

  • Thread starter Thread starter Jack Schitt
  • Start date Start date
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?
 
Hi
use
=SUMPRODUCT(array1,array2,...,arrayn)

This should be faster than the other two versions
 
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
 
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...
 
Back
Top