wynandbecker
New Member
- Joined
- Feb 23, 2021
- Messages
- 15
- Office Version
- 365
- Platform
- Windows
I am collecting HOURS WORKED from a bunch of DAILY sheets with predefined tasks on them, and summarizing them on SUMMARY sheet (per task).
On the DAILY sheets there are multiple instances of these tasks (there are four separate teams performing tasks every day).
In Summery!F2 I am collecting all the hours worked per-team-per-task with INDEX and MATCH, and trying to add these together with SUMIF.
It seems to work fine on cell Summery!F10, but the rest of them are giving very confusing results (for example it throws most of the HOURS WORKED under task NONE on the summary sheet, also if I change one of the tasks on the DAILY sheet to a different selection - it comes back with completely different numbers)
On the DAILY sheets there are multiple instances of these tasks (there are four separate teams performing tasks every day).
In Summery!F2 I am collecting all the hours worked per-team-per-task with INDEX and MATCH, and trying to add these together with SUMIF.
It seems to work fine on cell Summery!F10, but the rest of them are giving very confusing results (for example it throws most of the HOURS WORKED under task NONE on the summary sheet, also if I change one of the tasks on the DAILY sheet to a different selection - it comes back with completely different numbers)
Copy of DAILY DIARY & Invoicing - February 2021 LATEST.xlsx | |||||||||
---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | |||
7 | Teams | Main Task per project schedule | Equipment nr. | Equipment | Position | Target for the day | Results | ||
8 | Team 1 | Panel Mounting (Per panel) | 881-1100 | Conveyor | DMS | 4 | 3 | ||
9 | Team 1 | NONE | 881-1100 | Conveyor | DMS | 1 | |||
10 | Team 1 | NONE | 881-1100 | Conveyor | DMS | 1 | |||
11 | Team 2 | Panel Mounting (Per panel) | 881-1100 | Conveyor | DMS | 4 | 3 | ||
12 | Team 2 | NONE | 881-1100 | Conveyor | DMS | 1 | |||
13 | Team 2 | NONE | 881-1100 | Conveyor | DMS | 1 | |||
14 | Team 3 | Multicore Routing (Per team per day) - 4.65m per hour | 881-1100 | Conveyor | DMS | 40 | 45 | ||
15 | Team 3 | NONE | 881-1100 | Conveyor | DMS | 1 | |||
16 | Team 3 | NONE | 881-1100 | Conveyor | DMS | 1 | |||
17 | Team 4 | Panel Mounting (Per panel) | 120-3100 | Crusher | JIG Sec | 4 | 2 | ||
18 | Team 4 | Instrumentation Racking Install (Per team per day) - 1.4m/hour | 120-3100 | Crusher | JIG Sec | 12 | 1.5 | ||
19 | Team 4 | Multicore Routing (Per team per day) - 4.65m per hour | 120-3100 | Crusher | JIG Sec | 40 | 12 | ||
20 | Team 5 | Multicore Routing (Per team per day) - 4.65m per hour | 110-4100 | Apron | JIG Prim | 40 | 38 | ||
21 | Team 5 | NONE | 110-4100 | Apron | JIG Prim | 1 | |||
22 | Team 5 | NONE | 110-4100 | Apron | JIG Prim | 1 | |||
23 | Team 6 | Instrumentation Racking Install (Per team per day) - 1.4m/hour | 1100-4100 | Apron | JIG Prim | 12 | 5 | ||
24 | Team 6 | Multicore Routing (Per team per day) - 4.65m per hour | 1100-4100 | Apron | JIG Prim | 40 | 20 | ||
25 | Team 6 | NONE | 1100-4100 | Apron | JIG Prim | 1 | |||
26 | Team 7 | JB Mounting | 4 | ||||||
27 | Team 7 | Panel Mounting (Per panel) | 4 | ||||||
28 | Team 7 | NONE | 1 | ||||||
29 | Team 8 | NONE | 1 | ||||||
30 | Team 8 | NONE | 1 | ||||||
31 | Team 8 | NONE | 1 | ||||||
32 | Fabrication Team | On Site Cable Rack Modification (Per team per day) 2,9h/Mod | 3 | 3 | |||||
33 | Fabrication Team | NONE | 1 | ||||||
34 | Fabrication Team | NONE | 1 | ||||||
26 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
F8:F34 | F8 | =INDEX(Summery!$E$2:$E$28,MATCH('26'!B8,Summery!$B$2:$B$28,0)) |
Cells with Data Validation | ||
---|---|---|
Cell | Allow | Criteria |
B8:B34 | List | =Summery!$B$2:$B$28 |
Copy of DAILY DIARY & Invoicing - February 2021 LATEST.xlsx | ||||||||
---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | |||
1 | No. | Task Description | Target Hours per unit | Meters or Units per Unit | Unit Target for Day | 26 | ||
2 | 1 | Sensors Relocate; Spotface (Existing) | 0.6 | 14 | ||||
3 | 2 | Spotfacing Sensors (New) | 0.4 | 21 | ||||
4 | 3 | Sensor Mounting | 0.2 | 43 | ||||
5 | 4 | Sensor Routing (Conduit piping) | 0.5 | 17 | ||||
6 | 5 | JB Mounting | 2 | 4 | 0 | |||
7 | 6 | JB Termination (Per sensor) | 0.1 | 85 | ||||
8 | 7 | Panel Stands Fixing (Provisional) | 3 | 3 | ||||
9 | 8 | Panel Transport (Per panel) | 0.3 | 28 | ||||
10 | 9 | Panel Mounting (Per panel) | 2 | 4 | 8 | |||
11 | 10 | Multicore Routing (Per team per day) - 4.65m per hour | 40 | 40 | 38 | |||
12 | 11 | Multicore Terminations | 0.5 | 17 | ||||
13 | 12 | Power Cable Routing (Per team per day) - 4.65m per hour | 40 | 40 | ||||
14 | 13 | Power Cable Terminations | 1 | 9 | ||||
15 | 14 | Instrumentation Racking Install (Per team per day) - 1.4m/hour | 12 | 12 | 0 | |||
16 | 15 | On Site Cable Rack Modification (Per team per day) 2,9h/Mod | 3 | 3 | 0 | |||
17 | 16 | DB Mounting | 2 | 4 | ||||
18 | 17 | DB Termination | 0.3 | 28 | ||||
19 | 18 | DB Panel Stand (Provisional) | 3 | 3 | ||||
20 | 19 | DB Panel Stands Fixing (Provisional) | 3 | 3 | ||||
21 | 20 | DB Panel Transport | 0.3 | 28 | ||||
22 | 21 | DB Power Cable Routing | 40 | 40 | ||||
23 | 22 | DB Power Cable Terminations | 1 | 9 | ||||
24 | 23 | JB Panal Stand Manufacturing | 2 | 4 | ||||
25 | 24 | IMX Panal Stand Manufacturing | 4 | 2 | ||||
26 | 25 | IPDB Panal Stand Manufacturing | 4 | 2 | ||||
27 | 26 | Cherry Picker (Hours) | 1 | 1 | ||||
28 | 27 | NONE | 1 | 1 | 58 | |||
Summery |
Cell Formulas | ||
---|---|---|
Range | Formula | |
E2:E5,E7:E9,E23,E17:E21,E14,E12 | E2 | =8.5/C2 |
E6,E10,E24:E26 | E6 | =8/C6 |
F2:F28 | F2 | =IFERROR(SUMIF('26'!$B$8:$B$34,Summery!B2,INDEX('26'!$G$8:$G$34,MATCH(B2,'26'!$B$8:$B$34,0))),"") |