C
Chipperzs
I am using a similar SUMPRODUCT formula that uses logic tests agains
dates. Now my spreadsheet is very slow. I turned off the Auto Calculat
option and things speed up again so I know it's the formulas causing th
problems. Please take a look at my formula and let me know if there i
any what to speed it up.
On the first spreadsheet tab (Task List), I've got a series of column
[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 t
calculate the number of total tasks per month, and the total number o
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)*('Tas
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 hav
to add an extra date cell at the end to capture all the tasks betwee
12/1/2012 and 1/1/2013.
Then for the first row I use this formula to calculate the number o
tasks completed on time:[/FONT]
=SUMPRODUCT(('Task List'!$N:$N >= J3)*('Task List'!$N:$N< K3)*('Tas
List'!$P:$P=1)*('Task List'!$O:$O="Closed On Time"))+SUMPRODUCT(('Tas
List'!$N:$N >= J3)*('Task List'!$N:$N< K3)*('Task List'!$P:$P=1)*('Tas
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 tha
it's inoperative. Please let me know if there is a way to calculate thi
quicker (without VBA).
I've attached the file to help expedite the solution.
Thanks in advanc
+-------------------------------------------------------------------
|Filename: 2012 Task List - LouisSmith.zip
|Download: http://www.excelbanter.com/attachment.php?attachmentid=275
+-------------------------------------------------------------------
dates. Now my spreadsheet is very slow. I turned off the Auto Calculat
option and things speed up again so I know it's the formulas causing th
problems. Please take a look at my formula and let me know if there i
any what to speed it up.
On the first spreadsheet tab (Task List), I've got a series of column
[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 t
calculate the number of total tasks per month, and the total number o
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)*('Tas
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 hav
to add an extra date cell at the end to capture all the tasks betwee
12/1/2012 and 1/1/2013.
Then for the first row I use this formula to calculate the number o
tasks completed on time:[/FONT]
=SUMPRODUCT(('Task List'!$N:$N >= J3)*('Task List'!$N:$N< K3)*('Tas
List'!$P:$P=1)*('Task List'!$O:$O="Closed On Time"))+SUMPRODUCT(('Tas
List'!$N:$N >= J3)*('Task List'!$N:$N< K3)*('Task List'!$P:$P=1)*('Tas
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 tha
it's inoperative. Please let me know if there is a way to calculate thi
quicker (without VBA).
I've attached the file to help expedite the solution.
Thanks in advanc
+-------------------------------------------------------------------
|Filename: 2012 Task List - LouisSmith.zip
|Download: http://www.excelbanter.com/attachment.php?attachmentid=275
+-------------------------------------------------------------------