Thank you all for your feedback. When you look at something for too long,
you stop "seeing" what is there. My date comparisons (in Column B on the
Summary sheet and Column D on the Detail sheet) are comparing excel dates
(derived from =date(yyy,mm,dd). The problem seems to stem from incorrect
cell referencs - B1 should have been B7 (date field). I fixed that and also
made the columns absolute and now things seem to be working. Also, the idea
of using named ranges makes much more sense... wish I had thought of that!
You learn as you go and I appreciate all the help and knowledge you have
passed on to me. Now I know about this.

Until the next time... Thank You!
--
LPS


"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