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

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.


In article ,
Sam wrote:

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?

http://www.mrexcel.com/board2/viewto...=345161#345161

On Mar 10, 6:53 am, JE McGimpsey wrote:
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.

In article ,

Sam wrote:
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?