Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 - |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 - |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
nested countif | Excel Worksheet Functions | |||
sumif and countif nested? | Excel Worksheet Functions | |||
Using nested IFs with CountIf | Excel Worksheet Functions | |||
Nested Countif | Excel Worksheet Functions | |||
Countif Function -Nested | Excel Discussion (Misc queries) |