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]

Welcome to the forum Isabelle. I had a hard time understanding these as well, so you're not alone. At first glance it looks like your mixing up your data with the array you want to compare it to. I can probably solve your issue but would you mind providing a sample of the information your looking at in sheets 1 and 2? It's ok to change names, amounts etc. Also, rather than pressing just Enter, are you pressing Ctrl+Shft+Enter? This is what tells Excel to recognize an array formula and gives you the fancy {} brackets.

Regards,

D

 

[isabelle1989]

Thank you for the reply! Here is the information and thank you so very much!

This is a sample from sheet 1 (for reference headers are in row 1, Day/Time begins in column C, the Teacher 1 and Teacher 2 columns show when the student is actually with the teacher for example the third student is with Caryn from Saturday 10:30 - 11:00 only while the fourth student is with Caryn on Wednesday from 5:30 - 6:00 only and the fifth student is with Dawn on Wednesday from 5:30 - 6:00 and from 6:00 - 6:30 - I hope that makes sense):

Day/Time	Math	English	Teacher 1	Teacher 2
WED-4:30	X	X	Todd	Todd
WED-4:30	X	X	Caryn	Caryn
SAT-10:00		X		Caryn
WED-5:30	X		Caryn
WED-5:30	X	X	Dawn	Dawn
SAT-11:00	X	X	Todd	Todd
WED-4:30	X	X	Caryn	Caryn
SAT-10:00	X	X	Todd	Todd
SAT-10:30	X	X	Caryn	Caryn
THU-5:30	X	X	Todd	Todd
THU-6:30	X	X	Todd	Todd
WED-4:30	X	X	Caryn	Caryn
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

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

This is a sample from sheet 2 (for reference Caryn is at D6, Todd is at D7, and Dawn is at D8 while WED-4:30 is at E5):

WED-4:30

WED-5:30

WED-6:30

THU-4:30

THU-5:30

THU-6:30

SAT-10:00

SAT-11:00

Caryn

0

Todd

0

Dawn

0

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

 

[Drumstick22]

Hi Isabelle,

You want your formula to display an X on the time sheet on sheet 2, correct?

Try this formula and let me know how it works for you. You must use Ctrl+Shift+Enter for this to work, otherwise simply pressing Enter will give you an error.

=IFNA(IF(OR(Sheet1!$B$1:$B$21="X",Sheet1!$C$1:$C$21="X"),INDEX(Sheet1!$A$1:$E$21,MATCH(1,(Sheet1!$A$1:$A$21=Sheet2!E$5)*(Sheet1!$D$1:$D$21=Sheet2!$D6)*(Sheet1!$B$1:$B$21="X"),0),2),IF(OR(Sheet1!$B$1:$B$21="X",Sheet1!$C$1:$C$21="X"),INDEX(Sheet1!$A$1:$E$21,MATCH(1,(Sheet1!$A$1:$A$21=Sheet2!E$5)*(Sheet1!$D$1:$D$21=Sheet2!$D6)*(Sheet1!$C$1:$C$21="X"),0),3),)),"")

Regards,

D

 

[Drumstick22]

My answer may not been what you were looking for. What do you want your formula to display, and where do you want it located? I apologize if my first response did not answer your question.

Regards,

D

 

[isabelle1989]

Hi Drumstick,

I'm so sorry I wasn't very clear - on sheet two underneath each time and next to each teacher, I'd like it to let me know how many students are with that teacher at that particular time.

 

[Drumstick22]

Hi Isabelle,

No worries. I think this should work for you. Again, please use Ctrl+Shift+Enter. I entered this formula in cell E6 on Sheet 2. Our question is how many students on Wednesday at 6:30 for Caryn and the answer is 6. The formula can be copied across your table. Let me know if this works for you.

=SUMPRODUCT((Sheet1!$B$2:$B$21="X")*(Sheet1!$A$2:$A$21=Sheet2!E$5)*(Sheet1!$D$2:$D$21=Sheet2!$D6))+SUMPRODUCT((Sheet1!$C$2:$C$21="X")*(Sheet1!$A$2:$A$21=Sheet2!E$5)*(Sheet1!$E$2:$E$21=Sheet2!$D6))

Regards,

D

 

[isabelle1989]

Hi Drumstick,

This isn't working. One reason is that Caryn actually has 0 students at 6:30 on Wednesday. I'm really confused as to why you're using SUMPRODUCT - I thought I had to use INDEX and MATCH to basically find a total number based on 2 factors - time and teacher. Would you terribly mind explaining?

Thanks for your time.

Is

 

[Drumstick22]

Hi Drumstick,

This isn't working. One reason is that Caryn actually has 0 students at 6:30 on Wednesday. I'm really confused as to why you're using SUMPRODUCT - I thought I had to use INDEX and MATCH to basically find a total number based on 2 factors - time and teacher. Would you terribly mind explaining?

Thanks for your time.

Is

This is my mistake, I mean 4:30 on Wednesday Caryn has 6 students. If you want to be really fancy this will work too, again Ctrl+Shft+Enter:

="MATH"&" "&SUMPRODUCT((Sheet1!$B$2:$B$21="X")*(Sheet1!$A$2:$A$21=Sheet2!E$5)*(Sheet1!$D$2:$D$21=Sheet2!$D6))&" "&"ENGLISH"&" "&SUMPRODUCT((Sheet1!$C$2:$C$21="X")*(Sheet1!$A$2:$A$21=Sheet2!E$5)*(Sheet1!$E$2:$E$21=Sheet2!$D6))

 

[isabelle1989]

Hi again,

Caryn has 3 students at 4:30 on Wednesdays from the sample, so I'm really confused.

Is