XL2000 More Function Problems

[LPS]

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), I am using the following function
to calculate the number of courses taught by each instructor, from
"Evaluation Detail". It works perfectly in its original cell, representing
the month of September. When I copy it over to the total number of courses
cell for October, and change the date reference to October, it returns a
"False", even though there is data in the cells referenced by the function
(it should return a "1").

=IF(AND('Evaluation Detail'!$D$7:$D$500>=DATE(2008,10,1),'Evaluation
Detail'!$D$7:$D$500<=DATE(2008,10,31)),SUMPRODUCT(--('Evaluation
Detail'!$B$7:$B$500<>0),--('Evaluation Detail'!$F$7:$F$500="Linda
Sgabellone")))

I am getting very confused. Why does it work in one cell and not another,
when the cell references have not been changed - the only change is the date
value?

If anyone can explain this I would be very relieved and grateful.

 

[Bernie Deitrick]

LPS,

When you use the AND as the condition with a multi-row range, it is evaluated using only the values
on that row.

Try it this way instead:

=SUMPRODUCT(('Evaluation Detail'!$D$7:$D$500>=DATE(2008,10,1))*('Evaluation
Detail'!$D$7:$D$500<=DATE(2008,10,31))*('Evaluation Detail'!$B$7:$B$500<>0)*('Evaluation
Detail'!$F$7:$F$500="Linda Sgabellone"))


HTH,
Bernie
MS Excel MVP

 

[LPS]

Thank you Bernie. That did work. I had to change the date references on the
first sheet by using the =Date function, but it solved that problem.

Have a great day.