Code efficiency of Sumproduct()

[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?

 

[Frank Kabel]

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

This should be faster than the other two versions

 

[mzehr]

Hi Jack,
I'm not sure of the answer regarding efficiency, but for a
great explanation of sumproducts check out Bob Phillips
website:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

 

[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

 

[Jack Schitt]

Thanks to all 3. Much appreciated as always.

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