Sorry, I missed the day part:

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

Watch out for unwanted line-breaks.

Hope this helps.

Pete

On Dec 5, 7:29 pm, Pete_UK wrote:
Try it this way:

=SUMPRODUCT((evalsecondlang!D1:D65522="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

On Dec 5, 7:19 pm, LPS wrote:



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:D<"Catherine
Rousseau","",IF(evalsecondlang!E:E<"Monday","",IF (evalsecondlang!F:F="Morn--ing",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.

--
LPS- Hide quoted text -

- Show quoted text -