Working.xlsx | ||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | |||
1 | Login | Shift | Sun | Mon | Tue | Wed | Thu | Fri | Sat | Site | Rt-Fac | Day | Interval | HYD | 5:00-14:00 | 6:00-15:00 | 7:00-16:00 | |||
2 | davi | 5:00-14:00 | W | W | W | W | OFF | OFF | W | SJO | 3 | Sun | 13:00 | 0 | ||||||
3 | ljaab | 7:00-16:00 | OFF | W | W | W | W | W | OFF | HYD | 4 | Sun | 13:15 | 0 | ||||||
4 | mzm | 7:00-16:00 | OFF | OFF | W | W | W | W | W | BLR | 11 | Sun | 13:30 | 0 | ||||||
5 | ngulo | 13:00-22:00 | OFF | W | W | W | W | W | OFF | SJO | 11 | Sun | 13:45 | 0 | ||||||
6 | niset | 16:00-01:00 | W | W | W | W | W | OFF | OFF | HYD | 7 | Sun | 14:00 | 0 | ||||||
7 | rshia | 16:00-01:00 | OFF | W | W | W | W | W | OFF | HYD | 1 | Sun | 14:15 | 0 | ||||||
8 | tifa | 16:00-01:00 | W | OFF | OFF | W | W | W | W | HYD | 2 | Sun | 14:30 | 0 | ||||||
9 | esi | 6:00-15:00 | W | W | W | W | W | W | OFF | HYD | 4 | Sun | 14:45 | 0 | ||||||
10 | esur | 7:00-16:00 | OFF | OFF | W | W | W | W | W | SJO | 11 | Sun | 17:00 | 0 | ||||||
11 | ogo | 13:00-22:00 | W | W | W | W | W | OFF | OFF | SJO | 11 | Sun | 17:15 | 0 | ||||||
12 | asgai | 22:30-07:30 | OFF | W | W | W | W | W | OFF | HYD | 11 | Sun | 17:30 | 0 | ||||||
13 | audd | 22:30-07:30 | OFF | W | W | W | W | W | OFF | HYD | 11 | Sun | 17:45 | 0 | ||||||
14 | ibad | 9:00-18:00 | W | W | W | W | W | OFF | OFF | HYD | 5 | Sun | 18:00 | 0 | ||||||
15 | aher | 9:00-18:00 | W | W | W | OFF | OFF | W | W | SJO | 6 | Sun | 18:15 | 0 | ||||||
16 | anim | 13:00-22:00 | OFF | W | W | W | W | W | OFF | HYD | 11 | Sun | 18:30 | 0 | ||||||
17 | ly | 22:00-07:00 | OFF | W | W | W | W | W | OFF | VCC | 11 | Sun | 23:45 | 0 | ||||||
18 | ramo | 4:30-13:30 | OFF | OFF | W | W | W | W | W | BLR | 11 | Mon | 0:00 | 0 | ||||||
19 | ganr | 11:00-20:00 | OFF | W | W | W | W | W | OFF | HYD | 11 | Mon | 0:15 | 0 | ||||||
20 | goori | 22:00-07:00 | OFF | W | W | W | W | W | OFF | HYD | 11 | Mon | 0:30 | 0 | ||||||
21 | upoju | 6:00-15:00 | W | W | OFF | OFF | W | W | W | HYD | 11 | Mon | 0:45 | 0 | ||||||
22 | at | 20:00-05:00 | W | W | W | W | W | OFF | OFF | HYD | 11 | Mon | 1:00 | 0 | ||||||
Sheet1 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
O2:O22 | O2 | =SUMIFS($K$2:$K$22,$K$2:$K$22,">7",$J$2:$J$22,O$1,B2:B22,$P$1:$R$1,INDEX($C$2:$I$22,0,MATCH($M2,$C$1:$I$1,0)),"W") |
Press CTRL+SHIFT+ENTER to enter array formulas. |
Hello all, please help me with the above formula, when am trying to give the condition as range from P1:R1, it is not working, if I give the condition as single cell reference "P1" instead of "P1:R1" is is working.