Attendance rate is days present over days enrolled — or, from a row of P/A marks, the count of “P” over the total. COUNTIF tallies the marks without a helper column.
The example
18 present of 20 days.
| A | B | |
|---|---|---|
| 1 | Item | Value |
| 2 | Days present | 18 |
| 3 | Days enrolled | 20 → 90% |
The formula
The formula:
How it works
How it works:
COUNTIF(marks, "P")counts the days marked present.- Divide by
COUNTA(marks)— the total days with any mark — for the rate. - Or work from numbers:
days_present / days_enrolled. - Count absences the same way with
COUNTIF(marks, "A")for a chronic-absence flag.
Chronic absenteeism is often defined as missing 10% or more of enrolled days. Flag it with =IF(absences / enrolled >= 0.1, "At risk", "OK"). Counting tardies separately (a third mark, “T”) lets one row of data drive attendance, absence, and tardy reports at once.
Try it: interactive demo
Marks: P present, A absent (space/comma separated).
Variations
From numbers
Present ÷ enrolled:
Absence count
Days missed:
Chronic flag
10% rule:
Pitfalls & errors
Consistent marks. "P" must be typed the same everywhere — COUNTIF is case-insensitive but spacing matters.
Count the right denominator. Use enrolled days, not calendar days, so excused gaps don’t skew it.
Blanks. COUNTA skips empty cells; decide whether a blank means absent or not-yet-recorded.
Practice workbook
Frequently asked questions
How do I calculate attendance rate in Excel?
How do I count absences?
How do I flag chronic absenteeism?
Stop fighting formulas. Learn them in a day.
This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.
See the Formulas & Functions Class