First question: what is B1? My immediate thought is either that this should
be B7 OR B$1 (if you want to reference a fixed row).
Second: you said that the dates were formatted as Mmm-yy. Are they simply
displayed that way? Or are then entered as text in that format? The
comparison you are making is to text in that format, not to actual dates! If
the data is really dates, then you can (in fact must) compare to dates,
without the text( ) conversion; the different formatting affects only the
display, not the cell's value.

"LPS" wrote:

Refresher: My Excel 2000 workbook has two sheets, the first (Evaluation
Detail) contains details about course evalutions per instructor. Column A is
Course Name, B and C are Course Numbers, D is Course Date, E is Training
Provider, F is Instuctor Name, G thru P are evaluation scores and Q is
Evaluation Average.

On the second sheet (Evaluation Summary), Column A is Instructor Name,
Column B is the date in MMM-YY format, C is number of courses, D is number of
evaluations and E is Evaluation Average. In column E I want to calculate
the evaluation average per instructor, per month.

I have tried the following calculation but it does not return anything. I
have looked at it until my eyes are crossed but I cannot find why it is not
working. I think something is missing but... what???

Can anyone shed some much needed light on this? I appreciate all suggestions.

=IF(ISERROR(SUMPRODUCT(--('Evaluation
Detail'!$F$7:$F$500=A7),--(TEXT('Evaluation
Detail'!D$7:D$500,"mmm-yy")=TEXT(B1,"mmm-yy")),'Evaluation
Detail'!$Q$7:$Q$500)/SUMPRODUCT(--('Evaluation
Detail'!$F$7:$F$500=A7),--(TEXT('Evaluation
Detail'!D$7:D$500,"mmm-yy")=TEXT(B1,"mmm-yy")))),"",SUMPRODUCT(--('Evaluation
Detail'!$F$7:$F$500=A7),--(TEXT('Evaluation
Detail'!D$7:D$500,"mmm-yy")=TEXT(B1,"mmm-yy")),'Evaluation
Detail'!$Q$7:$Q$500)/SUMPRODUCT(--('Evaluation
Detail'!$F$7:$F$500=A7),--(TEXT('Evaluation
Detail'!D$7:D$500,"mmm-yy")=TEXT(B1,"mmm-yy"))))
--
LPS