Good day. I hope you are all doing safe during this time.
I really am not sure which function I should use to look up for values that I need in my worksheet.
I have a worksheet (BASE) containing the summary of schedules of all the teachers with their classes, date, day, time and language.
I have another worksheet (SUM) that I need to summarize everyone's schedule and see them all at a glance. I need to find (1) THE TEACHER and (2) THE DATE based on the class, day, time and language written.
I have used SUMPRODUCT to no avail. Appreciate if someone could assist in this.
Thank you so much and keep safe.
I really am not sure which function I should use to look up for values that I need in my worksheet.
I have a worksheet (BASE) containing the summary of schedules of all the teachers with their classes, date, day, time and language.
WORKING FILE.xlsx | ||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | |||
1 | WEEK NO. | 22 | ||||||||||||||||
2 | INCLUSIVE DATES: | MAY 25 - 30, 2021 | ||||||||||||||||
3 | TEACHER | CLASS | DATE | DAY | TIME | LANGUAGE | CLASS | DATE | DAY | TIME | LANGUAGE | CLASS | DATE | DAY | TIME | LANGUAGE | ||
4 | MICHAEL SAN JUAN | DOHA | 20-May-21 | THURS | 7:45 PM | TAGALOG | MESAIEED | 21-May-21 | FRI | 6:45 AM | TAGALOG | AMMAN | 21-May-21 | SUN | 11:00 AM | TAGALOG | ||
5 | ALICE DE GUZMAN | DUKHAN | 19-May-21 | WEDS | 7:45 PM | ENGLISH | DOHA | 22-May-21 | SAT | 7:45 PM | ENGLISH | DOHA | 19-May-21 | WEDS | 7:45 AM | ENGLISH | ||
6 | ||||||||||||||||||
7 | SCHEDULE 1 | SCHEDULE 2 | SCHEDULE 3 | |||||||||||||||
BASE |
I have another worksheet (SUM) that I need to summarize everyone's schedule and see them all at a glance. I need to find (1) THE TEACHER and (2) THE DATE based on the class, day, time and language written.
I have used SUMPRODUCT to no avail. Appreciate if someone could assist in this.
WORKING FILE.xlsx | |||||||||
---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | |||
1 | SUMMARY OF CLASS SCHEDULE | ||||||||
2 | |||||||||
3 | |||||||||
4 | WEEK NO.: | INCLUSIVE DATES: | |||||||
5 | |||||||||
6 | SUMMARY | ||||||||
7 | CLASS | DAY | TIME | LANGUAGE | DATE | TEACHER | SIGNATURE | ||
8 | DOHA | THURS | 7:45 PM | TAGALOG | #VALUE! | ||||
9 | DOHA | WEDS | 7:45 PM | TAGALOG | |||||
10 | DUKHAN | WEDS | 7:45 AM | ENGLISH | |||||
11 | MESAIEED | FRI | 9:00 PM | TAGALOG | |||||
12 | AMMAN | SAT | 11:00 AM | TAGALOG | |||||
SUM |
Cell Formulas | ||
---|---|---|
Range | Formula | |
F8 | F8 | =SUMPRODUCT(($A8:BASE!$B4=BASE!$B17)*($A8:BASE!G4=BASE!G17)*($A8:BASE!L4=BASE!L17)*($A8:BASE!Q4=BASE!Q17)*($A8:BASE!V4=BASE!V17)*($B8:BASE!$D4=BASE!$D17)*($B8:BASE!I4=BASE!I17)*($B8:BASE!N4=BASE!N17)*($B8:BASE!S4=BASE!S17)*($B8:BASE!X4=BASE!X17)*($C8:BASE!$B4=BASE!$B17)*($C8:BASE!J4=BASE!J17)*($C8:BASE!O4=BASE!O17)*($C8:BASE!T4=BASE!T17)*($C8:BASE!Y4=BASE!Y17)*($D8:BASE!$F4=BASE!$F17)*($D8:BASE!K4=BASE!K17)*($D8:BASE!P4=BASE!P17)*($D8:BASE!U4=BASE!U17)*($D8:BASE!Z4=BASE!Z17),BASE!A4:BASE!A17) |
Thank you so much and keep safe.