multiple criteria two sheets

[isabelle1989]

Thank you in advance for your help - I do not have a lot of experience in Excel but am trying to create a way to look quickly at pertinent information rather than scroll through a worksheet by hand. Specifically, I am trying to see how many students each teacher has at any given time. This is an after school program where the days and times that students come can change and we have limits of how many students each teacher can have.

After attempting to teach myself how to do this, I came up with this formula based on multiple "how-to" articles and forum posts:

=INDEX(Sheet1!$C$2:$G$100, MATCH(1, (Sheet1!$F$2:$F$100=Sheet2!D6)*(Sheet1!$C$2:$C$100=Sheet2!E5), 0), Sheet1!4)

This formula is attempting to single out one teacher and one time from an array of cells on a different worksheet. It is obviously wrong which is why I'm asking for help. If you need any other information, please ask. Please be kind, I've had no training in Excel at all.

With much thanks,

Isabelle

 

[Drumstick22]

Hi again!

Thanks so much for everything! Everything looks good except that sometimes we have students (not in the sample) who have class from 5:30 - 6:00 only, but they are still being counted in the second time slot. Any way to fix that?

I'm happy to help.

What would you like the amount to be for 1/2 hour students? Would you mind posting an example of your issue and what you would rather see?

Thanks,

D

 

[isabelle1989]

Thank you!

Here is a sample from sheet 1 - let's just look at the THU-5:30 students who have Caryn:

Time/Day Math English Teacher 1 Teacher 2

THU-5:30		X	Caryn
THU-5:30		X	Caryn
THU-5:30		X	Todd
THU-4:30	X		Caryn
WED-6:30	X	X	Todd	Todd
WED-6:30	X	X	Dawn	Dawn
WED-5:30	X	X	Caryn	Caryn
SAT-11:00	X	X	Todd	Todd
SAT-10:00	X	X	Todd	Todd
WED-5:30	X		Todd
THU-5:30	X			Caryn

<colgroup><col><col span="4"></colgroup><tbody>
</tbody>

Based on this data, Caryn has 2 students from 5:30 - 6:00 and 1 student from 6:00 - 6:30.

Here is the table from sheet 2 for Thursday for Caryn at 5:30 and at 6:00:

THU-5:30	THU-6:00
2	3

<!--StartFragment--> <colgroup><col width="65" span="2" style="width:65pt"> </colgroup><tbody>
<!--EndFragment--></tbody>

It looks to me as though the two students from 5:30 are being counted during the second half hour.

Thanks again!

Is

 

[Drumstick22]

Thank you!

Here is a sample from sheet 1 - let's just look at the THU-5:30 students who have Caryn:

Time/Day Math English Teacher 1 Teacher 2

THU-5:30		X	Caryn
THU-5:30		X	Caryn
THU-5:30		X	Todd
THU-4:30	X		Caryn
WED-6:30	X	X	Todd	Todd
WED-6:30	X	X	Dawn	Dawn
WED-5:30	X	X	Caryn	Caryn
SAT-11:00	X	X	Todd	Todd
SAT-10:00	X	X	Todd	Todd
WED-5:30	X		Todd
THU-5:30	X			Caryn

<tbody>
</tbody>

Based on this data, Caryn has 2 students from 5:30 - 6:00 and 1 student from 6:00 - 6:30.

Here is the table from sheet 2 for Thursday for Caryn at 5:30 and at 6:00:

THU-5:30	THU-6:00
2	3

<tbody>
</tbody>

It looks to me as though the two students from 5:30 are being counted during the second half hour.

Thanks again!

Is

If you put a filter on the table: With row 1 highlighted, click on the "Home" tab on the toolbar and on the far right side select "Sort & Filter" then "Filter". Dropdown column A and select only Thur-6:00 and add how many students Caryn has. Does it add up to 3?

 

[isabelle1989]

No - just 1