SumProduct function

[Guest]

I thought that by using the SUMPRODUCT function, rather than Array formulas,
the calculation process would be much quicker. However, with over 5000
functions, the calculation is dreadfully slow. Since I am looking up multiple
criteria, is there a benefit of running one type (SUMPRODUCT VS ARRAY) over
another?

Sam

 

[JE McGimpsey]

First, SUMPRODUCT is an array formula. It just doesn't require
CTRL-SHIFT-ENTER.

In general, SUMPRODUCT, when used as designed, e.g.,

=SUMPRODUCT(array1, array2)

is faster than either the corresponding

=SUMPRODUCT(array1 * array2)

or

{=SUM(array1*array2)}

the advantage can range from relatively small to significant.

I suspect that 5000 SUMPRODUCTS with multiple conditions will be
sluggish regardless...

Perhaps you can use calculate and store some intermediate values/arrays
to make calculation quicker.

 

[snasui]

http://www.mrexcel.com/board2/viewtopic.php?p=345161#345161