COUNTIFS / Attendance Clerk
Count Unexcused Absences
I need to count records where 'Type' is 'Absence' and 'Reason' is 'Unknown'.
formula.xlsx
=COUNTIFS(C2:C10,"Absence",D2:D10,"Unknown") How it works: The COUNTIFS function counts cells that meet multiple criteria. Here, it checks two conditions simultaneously: first, if the 'Type' column (C2:C10) contains 'Absence', and second, if the 'Reason' column (D2:D10) contains 'Unknown'. Only rows that satisfy both conditions are included in the final count, effectively identifying unexcused absences.
Data Setup
| Date | Student Name | Type | Reason |
|---|---|---|---|
| 2023-10-26 | Alice | Absence | Sick |
| 2023-10-26 | Bob | Present | N/A |
| 2023-10-26 | Charlie | Absence | Unknown |
| 2023-10-27 | Alice | Present | N/A |
| 2023-10-27 | Bob | Absence | Unknown |
| 2023-10-27 | Charlie | Absence | Sick |
| 2023-10-28 | Alice | Absence | Unknown |
| 2023-10-28 | Bob | Present | N/A |
| 2023-10-28 | Charlie | Absence | Unknown |
Step-by-Step Guide
1
Identify the range containing the 'Type' of attendance (e.g., Column C).
2
Identify the range containing the 'Reason' for attendance (e.g., Column D).
3
In an empty cell, enter the COUNTIFS function: `=COUNTIFS(C2:C10,"Absence",D2:D10,"Unknown")`
4
Press Enter to get the total count of unexcused absences.