EXcel 2000 IF-AND Statement

[LPS]

I have a workbook with two sheets: Evaluation Detail and Evaluation Summary.
On Evaluation Detail, I have a column with course numbers (Column B) and
another with Instructor Names (Column F). On the Evaluation Summary sheet I
want to count the number of course numbers if the Instructor name is
specific. I have created an IF-AND statement which includes the COUNTA
function (I have also tried using the COUNT function):

=IF(AND('Evaluation Detail'!$B$7:$B$499<>0,'Evaluation
Detail'!$F$7:$F$499="Linda Sgabellone"),COUNTA('Evaluation
Detail'!$B$7:$B$499),0)

This returns a result of 0 (zero), even though there are two occurrences
which should match. If I simply do a COUNTA, I get the correct answer but as
soon as I combine COUNTA (or COUNT) with the IF statement, it returns an
incorrect result.

Any suggestions?
All help is greatly appreciated.

 

[T. Valko]

Try this:

=SUMPRODUCT(--('Evaluation Detail'!$B$7:$B$499<>0),--('Evaluation
Detail'!$F$7:$F$499="Linda Sgabellone"))

 

[LPS]

Thanks so much, Biff. That worked perfectly!

Cheers,

 

[T. Valko]

You're welcome. Thanks for the feedback!