Count Lab Reports Submitted Late
As a science teacher, I need to efficiently track student punctuality for lab reports. Specifically, I want to count how many lab reports have been submitted (status 'Submitted') but were turned in after their official due date. This helps me identify trends in late submissions and provide timely feedback.
=COUNTIFS(E2:E8,"Submitted",D2:D8,">"&C2:C8) How it works: The COUNTIFS function counts cells that meet multiple criteria. Here, it first checks if the 'Status' column (E2:E8) contains 'Submitted'. Simultaneously, for each row that meets the first criterion, it checks if the 'Submission Date' (D2:D8) is greater than its corresponding 'Due Date' (C2:C8). The `">"&C2:C8` part dynamically compares each submission date to its respective due date, ensuring only truly late *submitted* reports are counted.
Data Setup
| Student Name | Assignment | Due Date | Submission Date | Status |
|---|---|---|---|---|
| Student A | Lab 1 | 2023-10-01 | 2023-09-30 | Submitted |
| Student B | Lab 1 | 2023-10-01 | 2023-10-01 | Submitted |
| Student C | Lab 1 | 2023-10-01 | 2023-10-02 | Submitted |
| Student D | Lab 2 | 2023-10-05 | 2023-10-04 | Submitted |
| Student E | Lab 2 | 2023-10-05 | 2023-10-06 | Submitted |
| Student F | Lab 3 | 2023-10-10 | 2023-10-09 | Graded |
| Student G | Lab 3 | 2023-10-10 | 2023-10-11 | Submitted |
| Student H | Lab 4 | 2023-10-15 | Not Submitted |
Step-by-Step Guide
Organize your lab report data in an Excel sheet with columns for 'Student Name', 'Assignment', 'Due Date', 'Submission Date', and 'Status'.
In an empty cell, enter the COUNTIFS formula: `=COUNTIFS(E2:E8,"Submitted",D2:D8,">"&C2:C8)`.
Press Enter to see the total count of lab reports that were submitted late.