Calculation speed slows down with SUMPRODUCT formulas

[schalkj]

I created a report sheet, using the following SUMPRODUCT formula: =SUMPRODUCT((Plant=$B8)*(InvoiceDate>=U$4+1)*(InvoiceDate<=V$4)*InvoiceAmount).


The report range consists of 81 columns by 250 rows, and the above formula is copied in each cell.


It takes Excel more than a minute (timed it) to recalculate each time. I have the same experience when I copy LOOKUP (V & HLookup) formulas to a large range (One model was still recalculating after I left it on for the night).


Question: What am I doing wrong for Excel to take so long to recalculate? Excel 2003, Windows XP Professional SP3

 

[Hui]

Schalkj


Sumproduct is one of several functions which are known as Volatile Functions.

These Volatile functions recalculate every time a spreadsheet change occurs as opposed to other functions that only recalculate when part of there precedence tree changes.


If your using Excel 2007/10 you can try replacing Sumproduct with Sumifs


For a more complete description of Volatile Functions check out:

http://www.decisionmodels.com/calcsecretsi.htm

 

[Chill]

Using Sum if as an array is an alternative for 2003 users. i.e =SUM(IF(Plant=$B8,if(InvoiceDate>=U$4+1,if(InvoiceDate<=V$4,InvoiceAmount)))) - remembering to press CTRL + SHIFT + ENTER.


Might be quicker?

 

[schalkj]

Thanks Hui, Chill.

Chill, I tried your sum(if ....) formula - no difference at all in the calculation speed.


Pivot table doesn't work: Does not group dates in months, says "cannot group data", and it leaves out entries.


Seems like I am stuck with having to wait.