Nested IF and Countif ?

[LPS]

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), 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]

Try it this way:

=SUMPRODUCT((evalsecondlang!D165522="Catherine
Rousseau")*(evalsecondlang!F1:F65522="Morn-ing")*(evalsecondlang!
H1:H65522=1))

You can't use a complete column with SUMPRODUCT, so I've made it cover
rows 1 to 65522 (almost a complete column, and easy to remember).

Hope this helps.

Pete

 

[LPS]

Please look here for corrected column references:

 

[Pete_UK]

Sorry, I missed the day part:

=SUMPRODUCT((evalsecondlang!D165522="Catherine
Rousseau")*(evalsecondlang!E1:E65522="Monday")*(evalsecondlang!
F1:F65522="Morning")*(evalsecondlang!H1:H65522=1))

Watch out for unwanted line-breaks.

Hope this helps.

Pete

 

[Pete_UK]

Look where?

Pete

Please look here for corrected column references:










- Show quoted text -

 

[LPS]

Thanks Pete... I'll give it a try.
--
LPS

Try it this way:

=SUMPRODUCT((evalsecondlang!D165522="Catherine
Rousseau")*(evalsecondlang!F1:F65522="Morn-ing")*(evalsecondlang!
H1:H65522=1))

You can't use a complete column with SUMPRODUCT, so I've made it cover
rows 1 to 65522 (almost a complete column, and easy to remember).

Hope this helps.

Pete

 

[Pete_UK]

You're welcome.

It would be better if you were to put the criteria in cells, and to
use the cell references in the formula, so that you can easily change
the criteria without having to change the formula.

Hope this helps.

Pete