EXcel 2000 IF-AND Statement
 

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

EXcel 2000 IF-AND Statement
 

Try this:

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

--
Biff
Microsoft Excel MVP


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

EXcel 2000 IF-AND Statement
 

Thanks so much, Biff. That worked perfectly!

Cheers,
--
LPS


"T. Valko" wrote:

Try this:

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

--
Biff
Microsoft Excel MVP


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

EXcel 2000 IF-AND Statement
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"LPS" wrote in message
...
Thanks so much, Biff. That worked perfectly!

Cheers,
--
LPS


"T. Valko" wrote:

Try this:

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

--
Biff
Microsoft Excel MVP


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