Logical
Course & onboarding completion matrix formula ("Complete", "In Progress 3/4", "Not Started")
Summarize explicit X completion marks for four modules and calculate the share of listed learners with every module marked.
Track explicit completion marks
Use X only when a module is complete; blanks and notes such as Pending must not count. COUNTIF(range,"X") makes this policy explicit. COUNTA would also count notes, spaces and other nonempty values.
The displayed rate measures marked completion among the four listed learners. It is not evidence of certification, attendance, learning quality or regulatory compliance. All four modules are assumed required for each listed person; optional modules require a different denominator.
| Employee (A) | Completed cells COUNTIF(B:E,"X") | Computed Status Label (F) |
|---|---|---|
| Maya (4 of 4 signed off) | 4 / 4 | "Complete" |
| Liam (2 of 4 signed off) | 2 / 4 | "In Progress (2/4)" |
| Noah (0 of 4 signed off) | 0 / 4 | "Not Started" |
| Zoe (4 of 4 signed off) | 4 / 4 | Complete (2 of 4 learners complete = 50%) |
Dynamic 3-state module completion status formula
COLUMNS counts the four cells in the stated range. When adding a module, extend the completion ranges and update the status-column references; do not assume a column added outside the range will be included.
=LET(done,COUNTIF(B3:E3,"X"),required,COLUMNS(B3:E3),IFS(done=0,"Not Started",done<required,"In Progress ("&done&"/"&required&")",TRUE,"Complete"))Completion rate for the four listed learners
Counts ONLY employees who have finished 100% of mandatory modules.
=ROUND(COUNTIF(F2:F5, "Complete") / ROWS(F2:F5), 4)Try the example
Use the original values or change them to test your understanding.