| 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.
