REVISED Nexted IF and Countif

[LPS]

Disregard easlier post. Column references were wrong. Here is corrected post:

Using Excel 2000:
I have one spreadsheet (titled “evalsecondlangâ€) that contains training
stats. On another spreadsheet in the same workbook, I need to use the data on
evalsecondlang to calculate the number of courses taught by a particular
instructor, on a particular day, in either the morning or afternoon. In
Evalsecondlang, Column D contains the instructors’ names. Column E contains
the day of the week. Column F contains either Morning or Afternoon. Column H
contains a list of numbers (e.g.; 1, 2, 3 and so on), Colunm G is blank e.g.;

D E F G H
Catherine Rousseau Monday Afternoon 3
Catherine Rousseau Monday Morning 2
Catherine Rousseau Monday Afternoon 3
Catherine Rousseau Monday Morning 1
Catherine Rousseau Monday Afternoon 3
Georges Farid Thursday Morning 4
Georges Farid Thursday Morning 3
Georges Farid Thursday Morning 4
Georges Farid Thursday Morning 3
Isabelle Poulin Thursday Morning 2
Isabelle Poulin Thursday Afternoon 1
Isabelle Poulin Thursday Afternoon 1

I want to create a nested IF statement which looks at column D and if the
instructor’s name is there, then it looks at Column E and looks for “Mondayâ€,
and if that is there then it looks in Column F for “Morning†and if that is
there, then counts the number of “1’s†in column H. I tried the following
and it doesn’t work; it returns a blank:

=IF(evalsecondlang!D<>"Catherine
Rousseau","",IF(evalsecondlang!E:E<>"Monday","",IF(evalsecondlang!F:F="Morning",COUNTIF(evalsecondlang!$H:$H,1)," ")))

This would indicate that the values I am looking for are not there, in the
sequence I want. But when I manually check the sheet, all the criteria is
there.

Can anyone please help me figure out what I am doing wrong?

Any and all help is greatly appreciated.

 

[Pete_UK]

No need to start another thread - better to continue with your other
one.

Besides, there doesn't seem to be any differences in the cell
references between the two threads, and if there were you should still
be able to make the adjustments yourself.

See your other thread for an answer to your query.

Pete

 

[LPS]

Your solution worked. Thank you very much.

Is there any way to delete a thread?

 

[Pete_UK]

No, it's in the archives for ever now ... <bg>

but thanks for feeding back.

Pete