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.
--
LPS

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))*('Evaluatio n Detail'!$B$7:$B$500<0)*('Evaluation
Detail'!$F$7:$F$500="Linda Sgabellone"))


HTH,
Bernie
MS Excel MVP


"LPS" wrote in message
...
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.
--
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.

--
LPS


"Bernie Deitrick" wrote:

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))*('Evaluatio n Detail'!$B$7:$B$500<0)*('Evaluation
Detail'!$F$7:$F$500="Linda Sgabellone"))


HTH,
Bernie
MS Excel MVP


"LPS" wrote in message
...
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.
--
LPS