Counting & Conditional Aggregation
Course Completion & Compliance Pass-Rate Summary With COUNTIFS
Calculate the share of identified learners who completed a sample module and passed a chosen score threshold.
Distinguish module completion from certified exam passing in LMS compliance trackers
This example counts identified learners whose status is Completed and whose quiz score meets a chosen threshold. It is a sample completion-and-score metric, not proof of certification or compliance with HIPAA, OSHA or any other policy. Actual requirements may include identity verification, additional modules, recency, practical assessment and local rules.
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 |
Sample Completion and Passing Score Percentage (%)
Divides multi-criteria COUNTIFS passes by enrolled roster size COUNTA(A2:A7), guarding empty rosters.
=IF(COUNTIF(A2:A7, "<>") = 0, "Empty Roster", ROUND(100 * COUNTIFS(A2:A7, "<>", C2:C7, "Completed", D2:D7, ">=" & F2) / COUNTIF(A2:A7, "<>"), 1))Headcount Requiring Quiz Retake or Course Completion
Subtracts certified compliant staff from total enrolled learners.
=IF(COUNTIF(A2:A7, "<>") = 0, "Empty Roster", COUNTIF(A2:A7, "<>") - COUNTIFS(A2:A7, "<>", C2:C7, "Completed", D2:D7, ">=" & F2))Try the example
Use the original values or change them to test your understanding.