Code to speed up sumproduct formulas

tbeast,

Welcome to the forum!
The problem is that your named ranges reference whole columns. For example, your named range "Dates" references =Data!$A:$A. That is causing the slowdown.

I'm guessing you did this so that data could be added and the formulas would auto-update. However, you can keep that functionality without referencing the whole column by using Dynamic Named Ranges.

So, your "Dates" named range could refer to this formula instead:
=Data!$A$1:INDEX(Data!$A:$A,COUNTA(Data!$A:$A))

Now the sumproduct formulas that use the "Dates" named range won't use the whole column, which should speed up their calculation considerably.

Here's some links to more information on dynamic named ranges:
http://support.microsoft.com/kb/830287
http://office.microsoft.com/en-us/ex...001126115.aspx
http://www.contextures.com/xlNames01.html
http://www.cpearson.com/excel/DefinedNames.aspx

tbeast,

Attached is a modified version of your posted workbook.
I updated all of the named range formulas.
I noticed that the ranges on the 'Data' sheet had some blank cells, so I based on the row count for all named ranges on 'Data' column A, so that they would all have the same row count as the named range "Dates". This was necessary because Sumproduct multiplies arrays together, and the arrays must have the same row count or the formula will return an error.

I also turned the calculation mode to automatic. Changing the week beginning or week ending sees the whole table on the 'Weekly' sheet get updated basically instantly.

Also, as a side note, because you're using a .xlsx file, you could replace all of your sumproduct() formulas with Sumif() formulas.

So instead of:

Please Login or Register  to view this content.

It would be:

Please Login or Register  to view this content.

Sumifs is optimized better and doesn't calculate like an array formula, so it would also serve to speed up calculation time. However, sumifs is not backwards compatible, so the formula would return a #NAME error for any user with Excel 2003 and lower.

lol, you're very welcome