Arrray vs Sumproduct

[MetLife]

Hi,

I got a spreadsheet that was created about 1 year ago and notice it uses a lot of arrays.

={Sum(O6:O156:AB6:AB156) + AM$7$}

When it could use

=Sumproduct(O6:O156,AB6:AB156) + AM$7$

Other then being confusing and less transparent, are arrays slower then sumproduct in this example above? I noticed the spreadsheet extremely slow.

Thanks

 

[Domski]

As far as I know the order of efficiency best to worst is:

1. Conditional Functions e.g. SUMIF, COUNTIF, SUMIFS, COUNTIFS
2. Sumproduct
3. Array Formula

There may be exceptions to this rule though I would guess.

Dom

 

[theBardd]

Leaving aside the many errors in your SUM formula, as this is just a straight use of SUMPRODUCT, that is not adding any conditional tests within it, I would expect SUMPRODUCT to easily out-perform the array SUM.