Hello all,
I've learned a lot over the years with excel, however I'm at a standstill on a particular problem. Long story short, I work for a school district, and need to provide total number of students each teacher has during any given day from our student information system.
The fastest way for me to export the data is to export all student schedules, and use countif to find how many a teacher appears in the raw data, the problem this year, is I've been asked to exclude students from some courses, and said courses may change per request of the report.
What I have so far is:
=SUM(COUNTIFS('HS Schedules'!O:O,I3,'HS Schedules'!N:N,INDIRECT(F1)))
'HS Schedules'!O:O = 6th hour teachers
I3 = teacher name.
'HS Schedules'!N:N = 6th hour classes
F1 = list of excluded courses I don't want to count.
F1 is built by a user placing a mark in a column, that then concatenates the value into a string. I concatenate the string result and format it as {"Footbal", "Golf"}
I can paste the value of F1 into
=SUM(COUNTIFS('HS Schedules'!O:O,I3,'HS Schedules'!N:N,{"Footbal", "Golf"}))
and it works, however I'm stuck at getting the formula to read the text value for the criteria
Open to thoughts or different suggestions.
Due to student data I cannot provide the worksheets, however; they are arranged as such:
HS Schedules
<tbody>
</tbody>
I've learned a lot over the years with excel, however I'm at a standstill on a particular problem. Long story short, I work for a school district, and need to provide total number of students each teacher has during any given day from our student information system.
The fastest way for me to export the data is to export all student schedules, and use countif to find how many a teacher appears in the raw data, the problem this year, is I've been asked to exclude students from some courses, and said courses may change per request of the report.
What I have so far is:
=SUM(COUNTIFS('HS Schedules'!O:O,I3,'HS Schedules'!N:N,INDIRECT(F1)))
'HS Schedules'!O:O = 6th hour teachers
I3 = teacher name.
'HS Schedules'!N:N = 6th hour classes
F1 = list of excluded courses I don't want to count.
F1 is built by a user placing a mark in a column, that then concatenates the value into a string. I concatenate the string result and format it as {"Footbal", "Golf"}
I can paste the value of F1 into
=SUM(COUNTIFS('HS Schedules'!O:O,I3,'HS Schedules'!N:N,{"Footbal", "Golf"}))
and it works, however I'm stuck at getting the formula to read the text value for the criteria
Open to thoughts or different suggestions.
Due to student data I cannot provide the worksheets, however; they are arranged as such:
HS Schedules
A | B | C | ... | N | O |
Student # | LastFirst | Period1 | ... | Period 6 Course | Period 6 Teacher |
123456 | lastfirst | 2D Art | ... | Football | Teacher_1 |
<tbody>
</tbody>