Challenge Scenario
In school administration and academic counseling, teachers track student daily attendance from Monday through Friday using simple check marks: "P" for Present and "A" for Absent.
Students whose weekly attendance falls below the 80% threshold (4 out of 5 days) must be flagged with an early warning notice to prevent chronic absenteeism.
As a Classroom Attendance Coordinator, your tasks are:
- Days Present (Column G): Count how many "P" days each student attended across Monday to Friday (
B2:F2) using COUNTIF(B2:F2, "P").
- Attendance % (Column H): Calculate the attendance percentage by dividing Days Present by total school days (
=G2 / 5).
- Status (Column I): If Attendance % is less than 80% (0.80), assign
"WARNING"; otherwise assign "GOOD".
- Total Present Days (Cell G8): Sum all present days across all students using
SUM(G2:G6).
Your Sheet Layout
Understanding how the student attendance roster is structured:
- Daily Attendance Grid (Columns A to F): Lists student names and their daily attendance marks for Monday, Tuesday, Wednesday, Thursday, and Friday.
- Weekly Totals & Status (Columns G to I): Counts present days, computes attendance percentage, and outputs early warning indicators.
- Class Attendance Summary (Cell G8): Grand total present days attended across the whole class (19 days).
| Cell Range |
Column Name |
Section |
Formula / Approach |
What It Does |
A2:F6 |
Student Name, Mon-Fri |
Daily Attendance |
Raw attendance check marks (P/A) |
Read-only daily attendance records. |
G2:G6 |
Days Present |
Calculation |
=COUNTIF(B2:F2, "P") |
Counts how many days the student was present ("P"). |
H2:H6 |
Attendance % |
Calculation |
=G2 / 5 |
Divides present days by 5 school days. |
I2:I6 |
Status |
Academic Alert |
=IF(H2 < 0.8, "WARNING", "GOOD") |
Flags students who attended less than 80% of the week. |
G8 |
TOTAL PRESENT DAYS |
Class Summary |
=SUM(G2:G6) |
Calculates the total attendance count across all students (19). |
Complete the objectives below in the live spreadsheet editor. Each objective card will turn green once your formula evaluates successfully.
1
In Column G (G2:G6), count present days for each student using COUNTIF(B2:F2, "P").
2
In Column H (H2:H6), calculate attendance percentage by dividing Days Present by 5 (=G2/5).
3
In Column I (I2:I6), assign status using IF: If Attendance % < 0.8, assign "WARNING", else "GOOD".
4
In cell G8, calculate total present days across all students using SUM(G2:G6).
Solution & Formula Breakdown
Let's examine how attendance counting and threshold evaluation work step by step:
1. Counting Present Days with COUNTIF (G2:G6)
=COUNTIF(B2:F2, "P")
The COUNTIF(range, criteria) function counts how many cells match "P":
- Row 2 (Alex Johnson): 5 "P" marks → 5 days.
- Row 3 (Bella Swan): 3 "P" marks → 3 days.
- Row 4 (Chris Evans): 4 "P" marks → 4 days.
- Row 5 (Diana Prince): 2 "P" marks → 2 days.
- Row 6 (Evan Wright): 5 "P" marks → 5 days.
2. Calculating Attendance Rate (H2:H6)
=G2 / 5
Divide Days Present by 5 total school days:
- Alex Johnson:
5 / 5 = 1.0 → 100%
- Bella Swan:
3 / 5 = 0.6 → 60%
- Chris Evans:
4 / 5 = 0.8 → 80%
- Diana Prince:
2 / 5 = 0.4 → 40%
- Evan Wright:
5 / 5 = 1.0 → 100%
3. Assigning Warning Status with IF (I2:I6)
=IF(H2 < 0.8, "WARNING", "GOOD")
If the attendance rate is strictly below 0.80 (80%), output "WARNING"; otherwise output "GOOD".
4. Class Total Attendance (Cell G8)
=SUM(G2:G6)
Adding all present days (5 + 3 + 4 + 2 + 5) yields 19 total student attendance days.
Using double quotes around text criteria like "P" is required in COUNTIF. Without quotes, Excel will treat P as an undefined cell name or formula variable!