Hi, I am currently using the following formula to calculate total hours worked at specified locations: VS or GG
Following is what I am using =SUMIF($C4:$C5, "VS",E4:E5)
Now as I am now dealing with more then one person, is there an easier way to have the same formula under each name (for the end of day), without having to fine tune to specific cells within a column of cells?
That way I can just copy and paste the formula for each day and under each person (editing the formula for the location).
Hope this makes sense, and welcome any suggestions to make an easier solution.
Following is what I am using =SUMIF($C4:$C5, "VS",E4:E5)
Now as I am now dealing with more then one person, is there an easier way to have the same formula under each name (for the end of day), without having to fine tune to specific cells within a column of cells?
That way I can just copy and paste the formula for each day and under each person (editing the formula for the location).
Hope this makes sense, and welcome any suggestions to make an easier solution.
| LocationHrs.xlsx | |||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | |||
| 1 | Cleaning & Maintenance | Total VS | Total GG | ||||||||||||||||||
| 2 | Day | Date | Locale | Type | Total Time | Day | Day | SubT | Wk | Day | Day | SubT | Wk | ||||||||
| 3 | CLvs | CLgg | TMvs | Tmgg | RPvs | RPgg | DLvs | CL | TM | CL | TM | ||||||||||
| 4 | Mon | 3-Apr-23 | VS | 2:00 | 3:00 | ||||||||||||||||
| 5 | Mon | 3-Apr-23 | GG | 1:00 | 3:00 | 2:00 | 3:00 | 5:00 | 1:00 | 3:00 | 4:00 | ||||||||||
| 6 | Tue | 4-Apr-23 | VS | 4:00 | |||||||||||||||||
| 7 | Tue | 4-Apr-23 | GG | 4:00 | 4:00 | 4:00 | 8:00 | 4:00 | 0:00 | 4:00 | |||||||||||
| 8 | Wed | 5-Apr-23 | VS | 3:00 | 8:00 | ||||||||||||||||
| 9 | Wed | 5-Apr-23 | GG | 5:00 | 3:00 | 5:00 | 8:00 | 5:00 | 0:00 | 5:00 | |||||||||||
| 10 | Thu | 6-Apr-23 | VS | 5:00 | 4:00 | ||||||||||||||||
| 11 | Thu | 6-Apr-23 | GG | 3:00 | 5:00 | 3:00 | 8:00 | 3:00 | 0:00 | 3:00 | |||||||||||
| 12 | Fri | 7-Apr-23 | VS | 1:00 | |||||||||||||||||
| 13 | Fri | 7-Apr-23 | GG | 1:00 | 0:00 | 1:00 | 0:00 | 0:00 | 0:00 | ||||||||||||
| 14 | End of Week | 46:00 | 30:00 | 16:00 | |||||||||||||||||
Sheet1 | |||||||||||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| L5,L13,L11,L9,L7 | L5 | =SUMIF($C4:$C5, "VS",E4:E5) |
| M5 | M5 | =SUMIF($C4:$C5, "VS",G4:G5) |
| N5,R13,N13,R11,N11,R9,N9,R7,N7,R5 | N5 | =SUM(L5:M5) |
| P5,P13,P11,P9,P7 | P5 | =SUMIF($C4:$C5, "GG",F4:F5) |
| Q5,Q13,Q11,Q9,Q7 | Q5 | =SUMIF($C4:$C5, "GG",H4:H5) |
| M7,M13,M11,M9 | M7 | =SUMIF($C6:$C7, "GG",F6:F7) |
| A4:A13 | A4 | =TEXT(B4,"ddd") |
| D14 | D14 | =SUM(O14+S14) |
| O14,S14 | O14 | =SUM(N4:N13) |
| Cells with Conditional Formatting | ||||
|---|---|---|---|---|
| Cell | Condition | Cell Format | Stop If True | |
| A5:C5 | Expression | =MOD(ROW(),2) | text | NO |
| A4:C4 | Expression | =MOD(ROW(),2) | text | NO |
| Q5:R5 | Expression | =MOD(ROW(),2) | text | NO |
| P5 | Expression | =MOD(ROW(),2) | text | NO |
| M5:N5 | Expression | =MOD(ROW(),2) | text | NO |
| L5 | Expression | =MOD(ROW(),2) | text | NO |
| O5 | Expression | =MOD(ROW(),2) | text | NO |
| Q13:R13 | Expression | =MOD(ROW(),2) | text | NO |
| P13 | Expression | =MOD(ROW(),2) | text | NO |
| N13 | Expression | =MOD(ROW(),2) | text | NO |
| L13 | Expression | =MOD(ROW(),2) | text | NO |
| M13 | Expression | =MOD(ROW(),2) | text | NO |
| M11 | Expression | =MOD(ROW(),2) | text | NO |
| M9 | Expression | =MOD(ROW(),2) | text | NO |
| M7 | Expression | =MOD(ROW(),2) | text | NO |
| E9:F9 | Expression | =MOD(ROW(),2) | text | NO |
| E8:F8 | Expression | =MOD(ROW(),2) | text | NO |
| Q11:R11 | Expression | =MOD(ROW(),2) | text | NO |
| P11 | Expression | =MOD(ROW(),2) | text | NO |
| N11 | Expression | =MOD(ROW(),2) | text | NO |
| L11 | Expression | =MOD(ROW(),2) | text | NO |
| O11 | Expression | =MOD(ROW(),2) | text | NO |
| Q9:R9 | Expression | =MOD(ROW(),2) | text | NO |
| P9 | Expression | =MOD(ROW(),2) | text | NO |
| Q7:R7 | Expression | =MOD(ROW(),2) | text | NO |
| P7 | Expression | =MOD(ROW(),2) | text | NO |
| N7 | Expression | =MOD(ROW(),2) | text | NO |
| L7 | Expression | =MOD(ROW(),2) | text | NO |
| N9 | Expression | =MOD(ROW(),2) | text | NO |
| L9 | Expression | =MOD(ROW(),2) | text | NO |
| K11 | Expression | =MOD(ROW(),2) | text | NO |
| E11:G11 | Expression | =MOD(ROW(),2) | text | NO |
| S11,H11:J11,A11:D11 | Expression | =MOD(ROW(),2) | text | NO |
| K9 | Expression | =MOD(ROW(),2) | text | NO |
| O9 | Expression | =MOD(ROW(),2) | text | NO |
| G9 | Expression | =MOD(ROW(),2) | text | NO |
| S9,A9:D9,H9:J9 | Expression | =MOD(ROW(),2) | text | NO |
| K7 | Expression | =MOD(ROW(),2) | text | NO |
| O7 | Expression | =MOD(ROW(),2) | text | NO |
| E7:G7 | Expression | =MOD(ROW(),2) | text | NO |
| S7,A7:D7,H7:J7 | Expression | =MOD(ROW(),2) | text | NO |
| K8 | Expression | =MOD(ROW(),2) | text | NO |
| L8:O8 | Expression | =MOD(ROW(),2) | text | NO |
| G8 | Expression | =MOD(ROW(),2) | text | NO |
| P8:S8,A8:D8,H8:J8 | Expression | =MOD(ROW(),2) | text | NO |
| K10 | Expression | =MOD(ROW(),2) | text | NO |
| R10 | Expression | =MOD(ROW(),2) | text | NO |
| P10 | Expression | =MOD(ROW(),2) | text | NO |
| Q10 | Expression | =MOD(ROW(),2) | text | NO |
| M10:N10 | Expression | =MOD(ROW(),2) | text | NO |
| L10 | Expression | =MOD(ROW(),2) | text | NO |
| O10 | Expression | =MOD(ROW(),2) | text | NO |
| E10:G10 | Expression | =MOD(ROW(),2) | text | NO |
| S10,H10:J10,A10:D10 | Expression | =MOD(ROW(),2) | text | NO |
| K12:K13,K4:K6 | Expression | =MOD(ROW(),2) | text | NO |
| O13 | Expression | =MOD(ROW(),2) | text | NO |
| S13 | Expression | =MOD(ROW(),2) | text | NO |
| S14 | Expression | =MOD(ROW(),2) | text | NO |
| R14 | Expression | =MOD(ROW(),2) | text | NO |
| O14 | Expression | =MOD(ROW(),2) | text | NO |
| Q14 | Expression | =MOD(ROW(),2) | text | NO |
| P14 | Expression | =MOD(ROW(),2) | text | NO |
| M14:N14 | Expression | =MOD(ROW(),2) | text | NO |
| L14 | Expression | =MOD(ROW(),2) | text | NO |
| M4:N4 | Expression | =MOD(ROW(),2) | text | NO |
| P4 | Expression | =MOD(ROW(),2) | text | NO |
| L4,O4,L6:O6,L12:O12 | Expression | =MOD(ROW(),2) | text | NO |
| E5:G5 | Expression | =MOD(ROW(),2) | text | NO |
| D5 | Expression | =MOD(ROW(),2) | text | NO |
| E4:G4,E12:G14,E6:G6 | Expression | =MOD(ROW(),2) | text | NO |
| D4,H14:K14,Q4:S4,P6:S6,S5,A12:D14,H12:J13,P12:S12,A6:D6,H4:J6 | Expression | =MOD(ROW(),2) | text | NO |
