Conditional sum gives a (runtime) FormulaArray error

[srinivas14]

I have a sheet with the following fields:
IMS No (Bug Id)
Assigned on (date on which assigned)
Planned Start (the date on which I planned to start fixing the bug)
Planned Close (the date on which I planned to finish fixing the bug)
Actual Start (the date on which I actually started fixing the bug, may be same/ different from plan date)
Actual Close (the date on which I actually finished fixing the bug, may be same/ different from plan date)
Closed after fix (the date on which it was verified and passed by the testing team)
Status (Open or Closed)
Time To Repair (TTR - Time taken from date assigned to date closed, i.e. Closed after fix-Planned Start+1)
Time To Fix (TTF - Time taken from actual start to actual end i.e. Actual Start - Actual End +1)
My problem is to find a mean of the TTR for all bugs planned to start AND also closed in this month. Note that some bugs may have started last month and closed this month and also some bugs started this month may slip into the next month. Such bugs are to be avoided for calculations.
As usual I went to Tools-> Wizard-> Conditional Sum. I selected the range ($A$1:$J$300). I have about 265 bugs, but just to be more general (generous?) I took 35 rows extra.
In Step 2 of the wizard, I chose Time To Repair as the column to sum, and added the following conditions:
Planned Start>=01-Mar-02
Planned Start<01-Apr-02
Closed after fix>=01-Mar-02
Closed after fix<01-Apr-02
In Step 3, I chose to Copy just the formula to a single cell.
In Step 4, I chose the Metrics!$B$10 as the target cell to contain the value. The moment I click on Finish, I get the following error:
Run-time error '1004':
Unable to set the FormulaArray property of the range class.
The end. Continue and Debug buttons are disabled. Help is of no use, as usual.
Let me add that I have checked for the validity of all dates. But where did I go wrong? Is there any settings problem? I tried with and without data filter, calculations- manual & automatic, giving the same results.
Please note that if the conditions are given individually or a combination of them, I am getting the results without any errors, although they are meaningless to me. It is only when I give all four together that I am facing this problem.

 

[Aladin Akyurek]

In Metrics!B10 enter:

=SUMPRODUCT((MONTH(C2:C265)=3)*(MONTH(G2:G265)=3),I2:I265)/MAX(1,SUMPRODUCT((MONTH(C2:C265)=3)*(MONTH(G2:G265)=3)))

where C2:C265 houses the Planned Start dates, G2:G265 the Closed After Fix dates, and I2:I265 the computed duration in days.

Note. Your data area appears to change (that is, it's not fixed). If you post 5 rows of your data including the labels like TTR and FTR, and the name of the sheet that houses your data, it's possible to devise a more effective formula(s) to use in your Metrics sheet. In order to post the sample data, go to the data sheet, activate an empty cell, type =, select 5 rows of your data, hit F9, copy what you see, and paste the copied bit in the follow up.

Aladin
This message was edited by Aladin Akyurek on 2002-04-10 01:44