|
REVISED Nexted IF and Countif
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 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 -- LPS |
REVISED Nexted IF and Countif
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 On Dec 5, 7:34 pm, LPS wrote: 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: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 -- LPS |
REVISED Nexted IF and Countif
Your solution worked. Thank you very much.
Is there any way to delete a thread? -- LPS "Pete_UK" wrote: 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 On Dec 5, 7:34 pm, LPS wrote: 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: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 -- LPS |
REVISED Nexted IF and Countif
No, it's in the archives for ever now ... <bg
but thanks for feeding back. Pete On Dec 5, 7:49 pm, LPS wrote: Your solution worked. Thank you very much. Is there any way to delete a thread? -- LPS "Pete_UK" wrote: 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 On Dec 5, 7:34 pm, LPS wrote: 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: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 -- LPS- Hide quoted text - - Show quoted text - |
| All times are GMT +1. The time now is 12:00 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com