sumproduct vs lookup

[mattflow]

Does anyone know if there is a significant difference between either the size of these two functions or the amount of time that it takes to excecute? I use sumproduct(--().) extensively in one of my worksheets and I am trying to reduce its size and calculation time. Any help would be appreciated.

Also, do circular references increase the calculation time significantly? It seems to me that they do. Thanks.

[Bryan Hessey]

Does anyone know if there is a significant difference between either the size of these two functions or the amount of time that it takes to excecute? I use sumproduct(--().) extensively in one of my worksheets and I am trying to reduce its size and calculation time. Any help would be appreciated.

Also, do circular references increase the calculation time significantly? It seems to me that they do. Thanks.

Hi,

without running time tests on large volumes it might be difficult to say, but I would think that a VLookup using one item and one array would be less impact than Sumproduct using one item on multiple arrays. The execution of VLookup is certainly less than SumProduct (view Tools, Formula Auditing, Evaluate Formula and step through each, 1 step for VLookup, many steps for (--(

The difference is usually in functionality rather than usage time, the VLookup and Match find the first occurance, the Sumproduct counts the number of occurances, perhaps a SumIf might be another consideration.

hth
---

[SystemsAccountant]

I have used vlookup, sumif, sumproduct, match & index, sum(if etc all extensively which large amounts of data. To get optimal performance I would suggest using SUMIF and build a string of the multiple critera you need to look up.

HTH
Matt

[DonkeyOte]

I agree entirely with Matt, if you're using a plethora of Sumproducts you should as you said yourself have a re-think as performance will be impacted...

I've posted a few times on the approach outlined by Matt recently, one here:

http://www.excelforum.com/excel-gene...ificantly.html

[edit] one here (bit more detailed)

http://www.excelforum.com/excel-gene...or-others.html

And another here:

http://www.mrexcel.com/forum/showthread.php?t=367386

I'm not trying to imply I'm an "expert" far from it merely that give a breakdown of the concatenation approach. On the latter thread Charles (FastExcel) posts a few links regards performance optimisation and he is widely regarded as one of the true experts when it comes to XL Calculation... ie this is his site: http://www.decisionmodels.com/calcsecrets.htm ... which is/should be on every XL developers bookmark list...

Matt -- why did you reply to an old post ... you've duped me into it too...

[NBVC]

FYI... the original posts are from Jan 30, 2007.....