Nested IF and Countif ?
 

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
instructors 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 €œ1s€� in column H. I tried the following
and it doesnt work; it returns a blank:

=IF(evalsecondlang!D:D<"Catherine
Rousseau","",IF(evalsecondlang!E:E<"Monday","",IF (evalsecondlang!F:F="Morning",COUNTIF(evalsecondla ng!$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

Nested IF and Countif ?
 

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

REVISED Nested IF and Countif ?
 

Please look here for corrected column references:

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
instructors 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 €œ1s€� in column H. I tried the following
and it doesnt work; it returns a blank:

=IF(evalsecondlang!D:D<"Catherine
Rousseau","",IF(evalsecondlang!E:E<"Monday","",IF (evalsecondlang!F:F="Morning",COUNTIF(evalsecondla ng!$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

Nested IF and Countif ?
 

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 -

REVISED Nested IF and Countif ?
 

Look where?

Pete

On Dec 5, 7:30 pm, LPS wrote:
Please look here for corrected column references:



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 -

Nested IF and Countif ?
 

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


"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

Nested IF and Countif ?
 

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

On Dec 5, 7:38 pm, LPS wrote:
Thanks Pete... I'll give it a try.
--
LPS



"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 -