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,

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

Is

I'm sorry I assumed if there was an X in Math and an X in English that meant two columns. This should fix it:

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

I will be happy to explain once we find the right formula for you.

Regards,

D

 

[isabelle1989]

Yay - it is working for all of the exact times. I tried to put in the formula for the 1/2 hours though but it doesn't work. There are some students for example who have class with Caryn only for 1/2 hour on Thursday at 6:00 so her name is only in the teacher 2 spot.

Is

 

[Drumstick22]

Yay - it is working for all of the exact times. I tried to put in the formula for the 1/2 hours though but it doesn't work. There are some students for example who have class with Caryn only for 1/2 hour on Thursday at 6:00 so her name is only in the teacher 2 spot.

Is

Great!! Glad to hear we're on the right path! This one should address the Teacher 2 column issue. Try the code below using Ctrl+Shift+Enter.

=(SUMPRODUCT((Sheet1!$A$2:$A$21=Sheet2!E$5)*(Sheet1!$D$2:$D$21=Sheet2!$D6))+SUMPRODUCT((Sheet1!$A$2:$A$21=Sheet2!E$5)*(Sheet1!$E$2:$E$21=Sheet2!$D6)))-(SUMPRODUCT((Sheet1!$D$2:$D$21=Sheet2!$D6)*(Sheet1!$E$2:$E$21=Sheet2!$D6)*(Sheet1!$A$2:$A$21=Sheet2!E$5)))

D

 

[isabelle1989]

Hi,

When I put in this formula, everyone has 3 students.

Is

 

[Drumstick22]

Hi,

When I put in this formula, everyone has 3 students.

Is

Press F9.

 

[isabelle1989]

I'm on a Mac

 

[Drumstick22]

I'm on a Mac

If you have a "Fn" key press that and locate the small "F9" on your keyboard. If not, try pressing CMD + =. If all else fails, select the "Formulas" tab on the toolbar. Then select "Calculate Now" on the far right side.

 

[Drumstick22]

If you have a "Fn" key press that and locate the small "F9" on your keyboard. If not, try pressing CMD + =. If all else fails, select the "Formulas" tab on the toolbar. Then select "Calculate Now" on the far right side.

If this doesn't work make sure the $ signs are positioned correctly in the formula. $A$1 means the reference cell in that formula fixed and will not change if you copy to another cell, $A1 = Rows will change but the columns stay fixed. A$1 = Columns will Change but the rows will stay fixed.

 

[Drumstick22]

Hi,

When I put in this formula, everyone has 3 students.

Is

I think I understand what our problem is after re-reading your comment.

The formula I provided was only to be entered in cell E6, or whichever cell on your spreadsheet intersects between Caryn and WED-4:30. After you paste this formula into E6, copy and paste it across your entire table (or click on the lower right corner of the box and drag the cell so it copies across and down). Otherwise if you paste it exactly as it is for each individual cell will only reference Caryn for WED-4:30, which will always equal 3.

Please let me know if this helps.

Regards,

D

 

[isabelle1989]

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?