Arte SheetsLearn. Try. Understand.

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 CriteriaMatching LearnersCompliance Percentage
Certified Pass Rate (Cutoff F2 = 80)Completed AND Score >= 803 of 6 Staff50.0%
Relaxed Pass Cutoff (Cutoff F2 = 70)Completed AND Score >= 704 of 6 Staff66.7%
Actionable Remediation CountTotal Enrolled - Certified Passes3 Staff PendingNeeds 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.

Further reading