I am using a similar SUMPRODUCT formula that uses logic tests against dates. Now my spreadsheet is very slow. I turned off the Auto Calculate option and things speed up again so I know it's the formulas causing the problems. Please take a look at my formula and let me know if there is any what to speed it up.


On the first spreadsheet tab (Task List), I've got a series of columns [Priority, Date Opened, Date Aging, Due date, Day's remaining, Program, Description, Current Action, Milestone, Notes:, Supports Measure, Closed, Status, % Complete, Hours to complete]

On a second tab (Task List Trends) I'm using a sumproduct formula to calculate the number of total tasks per month, and the total number of tasks completed on time.

I've listed dates in one row 3 [1/1/2012, 2/1/2012, 3/1/2012...]
In row 2 I use the sumproduct formula:


=SUMPRODUCT(('Task List'!$N:$N=J3)*('Task List'!$N:$N<K3)*('Task List'!$P:$P=1))
Where Column N = "Closed" or the date the tasks was closed[/font]
Column P = % Complete[/font]
J3 = 1/1/2012[/font]
K3 = 2/1/2012[/font]

I copy this formula across 12 cells for each month of the year. I have to add an extra date cell at the end to capture all the tasks between 12/1/2012 and 1/1/2013.

Then for the first row I use this formula to calculate the number of tasks completed on time:[/font]
=SUMPRODUCT(('Task List'!$N:$N = J3)*('Task List'!$N:$N< K3)*('Task List'!$P:$P=1)*('Task List'!$O:$O="Closed On Time"))+SUMPRODUCT(('Task List'!$N:$N = J3)*('Task List'!$N:$N< K3)*('Task List'!$P:$P=1)*('Task List'!$O:$O="Closed Early"))


Where Column N = "Closed" or the date the tasks was closed
Column P = % Complete
Column O = Status [Open, Closed On Time, Closed Early, Closed Late]

J3 = 1/1/2012

K3 = 2/1/2012


Both of these formulas work but now the spread sheet is so slow that it's inoperative. Please let me know if there is a way to calculate this quicker (without VBA).

I've attached the file to help expedite the solution.

Thanks in advance

I would suggest that you rewrite your sumproduct formulas to ONLY use
the range needed instead of the entire column

On Jan 11, 3:29*pm, Chipperzs
wrote:
I am using a similar SUMPRODUCT formula that uses logic tests against
dates. Now my spreadsheet is very slow. I turned off the Auto Calculate
option and things speed up again so I know it's the formulas causing the
problems. Please take a look at my formula and let me know if there is
any what to speed it up.

On the first spreadsheet tab (Task List), I've got a series of columns
[Priority, Date Opened, Date Aging, Due date, Day's remaining, Program,
Description, Current Action, Milestone, Notes:, Supports Measure,
Closed, Status, % Complete, Hours to complete]

On a second tab (Task List Trends) I'm using a sumproduct formula to
calculate the number of total tasks per month, and the total number of
tasks completed on time.

I've listed dates in one row 3 [1/1/2012, 2/1/2012, 3/1/2012...]
In row 2 I use the sumproduct formula:

=SUMPRODUCT(('Task List'!$N:$N=J3)*('Task List'!$N:$N<K3)*('Task
List'!$P:$P=1))
Where Column N = "Closed" or the date the tasks was closed[/font]
Column P = % Complete[/font]
J3 = 1/1/2012[/font]
K3 = 2/1/2012[/font]

I copy this formula across 12 cells for each month of the year. I have
to add an extra date cell at the end to capture all the tasks between
12/1/2012 and 1/1/2013.

Then for the first row I use this formula to calculate the number of
tasks completed on time:[/font]
=SUMPRODUCT(('Task List'!$N:$N = J3)*('Task List'!$N:$N< K3)*('Task
List'!$P:$P=1)*('Task List'!$O:$O="Closed On Time"))+SUMPRODUCT(('Task
List'!$N:$N = J3)*('Task List'!$N:$N< K3)*('Task List'!$P:$P=1)*('Task
List'!$O:$O="Closed Early"))

Where Column N = "Closed" or the date the tasks was closed
Column P = % Complete
Column O = Status [Open, Closed On Time, Closed Early, Closed Late]

J3 = 1/1/2012

K3 = 2/1/2012

Both of these formulas work but now the spread sheet is so slow that
it's inoperative. Please let me know if there is a way to calculate this
quicker (without VBA).

I've attached the file to help expedite the solution.

Thanks in advance

+-------------------------------------------------------------------+
|Filename: 2012 Task List - LouisSmith.zip * * * * * * * * * * * * *|
|Download:http://www.excelbanter.com/attachment.php?attachmentid=275|
+-------------------------------------------------------------------+

--
Chipperzs