Spreadsheet: 868430d1715169851-formula-to-get-actual-numbers-upon-meeting-conditions-sample-updated-majana54-
Sheet: Sheet1

 |  |  |  |  |  |  |  |  | [NAME] to get actual numbers upon meeting conditions (excelforum.com)
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Expected Result
Finance Employee List |  |  |  |  |  |  |  |  | Report for Completed KPI |  |  |  |  |  |  |  | KPI Weekly Team Report
Department | Team | Emp Name | Emp ID | Role | Delagate if on Annual Leave |  |  |  | Emp Name | Emp ID | Activity | Completion Date | Week | Delegated | Assigned |  | Role | Team | Activity | Target | Actual | Week # | Actual by [NAME]
Finance | Payroll | Emp 1 | ID001 | Manager | ID002 |  |  |  | Emp 2 | ID002 | KPI 1 | 2024-01-02 00:00:00 |  |  |  |  | Manager | Payroll | KPI 1 | 1 | 1 | 1 |  | For week 1, since Emp1 is on leave and the delegate is Emp2, everything that Emp2 does will go to Emp1 stats until the target is reached. Once the target is reached, the excess will go to Emp2's stats.
Finance | Payroll | Emp 2 | ID002 | Supervisor | ID003 |  |  |  | Emp 2 | ID002 | KPI 1 | 2024-01-03 00:00:00 |  |  |  |  | Supervisor | Payroll | KPI 1 | 2 | 2 | 1
Finance | Payroll | Emp 3 | ID003 | Assistant | N/A |  |  |  | Emp 2 | ID002 | KPI 1 | 2024-01-03 00:00:00 |  |  |  |  | Supervisor | Payroll | KPI 2 | 1 | 1 | 1
 |  |  |  |  |  |  |  |  | Emp 2 | ID002 | KPI 2 | 2024-01-04 00:00:00 |  |  |  |  | Manager | Payroll | KPI 3 | 1 | 1 | 1
 |  |  |  |  |  |  |  |  | Emp 2 | ID002 | KPI 3 | 2024-01-06 00:00:00 |  |  |  |  | Supervisor | Payroll | KPI 3 | 1 | 0 | 1
Weekly KPI Target per Role |  |  |  |  |  |  |  |  | Emp 2 | ID002 | KPI 1 | 2024-01-08 00:00:00 |  |  |  |  | Manager | Payroll | KPI 1 | 1 | 1 | 2
Role | Activity | Target |  |  |  |  |  |  | Emp 2 | ID002 | KPI 1 | 2024-01-08 00:00:00 |  |  |  |  | Supervisor | Payroll | KPI 1 | 2 | 2 | 2
Manager | KPI 1 | 1 |  |  |  |  |  |  | Emp 2 | ID002 | KPI 1 | 2024-01-08 00:00:00 |  |  |  |  | Supervisor | Payroll | KPI 2 | 1 | 0 | 2
Supervisor | KPI 1 | 2 |  |  |  |  |  |  | Emp 2 | ID002 | KPI 3 | 2024-01-10 00:00:00 |  |  |  |  | Manager | Payroll | KPI 3 | 1 | 1 | 2
Supervisor | KPI 2 | 1 |  |  |  |  |  |  | Emp 2 | ID002 | KPI 3 | 2024-01-10 00:00:00 |  |  |  |  | Supervisor | Payroll | KPI 3 | 1 | 1 | 2
Manager | KPI 3 | 1 |  |  |  |  |  |  | Emp 2 | ID002 | KPI 1 | 2024-01-15 00:00:00 |  |  |  |  | Manager | Payroll | KPI 1 | 1 | 1 | 3 |  | For week 3, [NAME] is on leave and the delegate is Emp3. Everything that Emp3 do will only be counted from the start of Emp2's leave date. Anything Emp3 completed not within the time of delegation is ignored.
Supervisor | KPI 3 | 1 |  |  |  |  |  |  | Emp 2 | ID002 | KPI 2 | 2024-01-15 00:00:00 |  |  |  |  | Supervisor | Payroll | KPI 1 | 2 | 1 | 3
 |  |  |  |  |  |  |  |  | Emp 1 | ID001 | KPI 1 | 2024-01-17 00:00:00 |  |  |  |  | Supervisor | Payroll | KPI 2 | 1 | 1 | 3
 |  |  |  |  |  |  |  |  | Emp 1 | ID001 | KPI 3 | 2024-01-18 00:00:00 |  |  |  |  | Manager | Payroll | KPI 3 | 1 | 1 | 3
Finance Leave Schedule |  |  |  |  |  |  |  |  | Emp 3 | ID003 | KPI 1 | 2024-01-15 00:00:00 |  |  |  |  | Supervisor | Payroll | KPI 3 | 1 | 1 | 3
Emp Name | Emp ID | Start Date | End Date | Start Week | End Week | Assign to: |  |  | Emp 3 | ID003 | KPI 2 | 2024-01-15 00:00:00
Emp 1 | ID001 | 2024-01-01 00:00:00 | 2024-01-14 00:00:00 |  |  |  |  |  | Emp 3 | ID003 | KPI 2 | 2024-01-16 00:00:00
Emp 2 | ID002 | 2024-01-16 00:00:00 | 2024-01-17 00:00:00 |  |  |  |  |  | Emp 3 | ID003 | KPI 3 | 2024-01-18 00:00:00