Counting & Conditional Aggregation
Course Completion & Compliance Pass-Rate Summary With COUNTIFS
Calculate LMS training compliance rates (%) requiring both Status = "Completed" and Exam Score >= Passing Cutoff using COUNTIFS(C2:C7, "Completed", D2:D7, ">=" & F2) / COUNTA(A2:A7).
Distinguish module completion from certified exam passing in LMS compliance trackers!
Corporate InfoSec, HIPAA, and OSHA training rosters frequently confuse "Watched All Slides" (Status = "Completed") with true compliance certification—which requires BOTH Status = "Completed" AND Exam Score >= Passing_Score (such as 80%)! Counting Status alone overstates compliance whenever an employee finishes the video but scores 68% on the quiz.
Pairing COUNTIFS(C2:C7, "Completed", D2:D7, ">=" & F2) with total enrolled headcount COUNTA(A2:A7) produces an exact certified compliance percentage while dynamically updating whenever the passing cutoff F2 changes! Across 6 enrolled staff (4 "Completed" with scores 92, 84, 72, 88; 1 "In Progress" with 0; 1 "Not Started" blank), raw completion is 4/6 = 66.7%, whereas certified pass compliance at F2 = 80 is 3/6 = 50.0%!
| Compliance Metric (6 Enrolled Learners) | COUNTIFS / COUNTA Criteria | Matching Learners | Compliance Percentage |
|---|---|---|---|
| Certified Pass Rate (Cutoff F2 = 80) | Completed AND Score >= 80 | 3 of 6 Staff | 50.0% |
| Relaxed Pass Cutoff (Cutoff F2 = 70) | Completed AND Score >= 70 | 4 of 6 Staff | 66.7% |
| Actionable Remediation Count | Total Enrolled - Certified Passes | 3 Staff Pending | Needs Retake or Reminder |
Certified Course Compliance Pass Percentage (%)
Divides multi-criteria COUNTIFS passes by enrolled roster size COUNTA(A2:A7), guarding empty rosters.
=IF(COUNTA(A2:A7) = 0, "Empty Roster", ROUND(100 * COUNTIFS(C2:C7, "Completed", D2:D7, ">=" & F2) / COUNTA(A2:A7), 1))Headcount Requiring Quiz Retake or Course Completion
Subtracts certified compliant staff from total enrolled learners.
=IF(COUNTA(A2:A7) = 0, "Empty Roster", COUNTA(A2:A7) - COUNTIFS(C2:C7, "Completed", D2:D7, ">=" & F2))Try the example
Use the original values or change them to test your understanding.