If each student’s daily attendance marks are in B2:AF2 and P means present, use =COUNTIF(B2:AF2,"P") to count days present; if 18 cells contain P, the result is 18. The formula counts matching marks in the selected range, so the right range and a consistent attendance code are essential.
How the attendance formula works
Excel’s COUNTIF function counts cells in a range that meet one condition. Its structure is a range followed by a criterion: the range is the group of cells to check, and the criterion is the value to count. In the example above, B2:AF2 covers one student’s attendance marks for up to 31 days, while P is the exact mark being counted.
COUNTIF does not decide whether a mark represents attendance; it simply matches cell contents. If your sheet uses Present, use that word as the criterion. If it uses 1 for present, count 1 instead. Keep the spelling and codes consistent: a cell containing Present will not match a criterion of P. Text matching with COUNTIF is not case-sensitive, but extra spaces or different words can cause unexpected results.
The formula counts marks, not necessarily calendar days. If each column represents one school day, the total is days present. If columns represent separate classes or morning and afternoon sessions, the result is sessions attended. Decide what one cell represents before interpreting the total.
Set up a simple attendance sheet
A common monthly layout has one student per row, dates across the columns, and a short code in each date cell. Put student names in column A, date headings in B1:AF1, and attendance marks in B2:AF100. This makes each student’s marks a continuous row that can be counted without including the heading row or another student’s data.
- Choose a small set of codes, such as P for present, A for absent, and L for late.
- Use one code consistently for each outcome. Avoid mixing P, Present, and check marks unless the formulas account for each one.
- Leave future dates blank rather than marking them absent. A blank should mean no recorded attendance, not an absence.
- Keep dates in the headings so the marks can be interpreted later, even though COUNTIF only needs the mark range.
For fewer typing errors, Excel’s Data Validation feature can restrict entries to approved codes. Select the cells for attendance marks, then choose Data > Data Validation and set Allow to List; enter the permitted codes in the Source field. Menu wording can vary slightly by Excel build, but Data Validation is available in Microsoft 365 for Windows and Mac. A drop-down is especially useful when several people update the same workbook.
Count present days for each student
With one student’s daily marks in B2:AF2, P used for present, and 18 P marks among the 31 dates, enter =COUNTIF(B2:AF2,"P") in a total cell on row 2; the result is 18. Put the total outside the attendance range, for example in AG2, so the formula does not count itself. Copy it down the total column for the other students. In Excel, use the fill handle at the lower-right corner of the selected formula cell, or copy and paste the formula into the rows below.
When copied down, a relative reference such as B2:AF2 adjusts to B3:AF3, then B4:AF4. That is useful when the same monthly layout applies to every student. Check the first few copied results against the marks on those rows before relying on a large roster total.
If the cells contain the full word Present instead of P, with that student’s marks still in B2:AF2, use =COUNTIF(B2:AF2,"Present"). It counts cells whose value matches Present. It does not count a cell containing Present – late as an exact match. Codes are usually easier to enter and count, while full words may be easier for occasional readers to understand.
Count absences, late marks, and other codes
With the same layout—one student’s marks in B2:AF2 and A marking an absence—use =COUNTIF(B2:AF2,"A") to count absences. If there are 10 A marks, the result is 10. Place this total in a separate summary column, such as AH, rather than inside B:AF.
With B2:AF2 containing the same student’s marks and L representing late, use =COUNTIF(B2:AF2,"L") to count late marks. It returns the number of cells marked L, not the number of minutes late. If the school’s rules treat late arrivals as attended, keep late counts visible but make an explicit choice about whether to include them in the attendance rate.
Some sheets use separate codes for excused and unexcused absences. Count each code separately if those distinctions matter for reporting. Do not combine codes merely because they look similar: an excused absence and a present mark have different meanings, even if both are treated differently from an unexcused absence in a local policy.
Calculate an attendance percentage
A percentage needs a clear denominator: count the sessions that actually took place and were recorded, rather than automatically dividing by every date in a month. With P for present, A for absent, L for late, and this student’s marks in B2:AF2, use =COUNTIF(B2:AF2,"P")/(COUNTIF(B2:AF2,"P")+COUNTIF(B2:AF2,"A")+COUNTIF(B2:AF2,"L")). If the row contains 18 P marks, 10 A marks, 2 L marks, and one blank, the calculation is 18 divided by 30, or 60%.
This example treats late as a recorded session in the denominator but not as a present mark in the numerator. If the reporting rule counts late students as present, the numerator needs to include L as well. With the same layout and rule that both P and L count as attended, use =(COUNTIF(B2:AF2,"P")+COUNTIF(B2:AF2,"L"))/(COUNTIF(B2:AF2,"P")+COUNTIF(B2:AF2,"A")+COUNTIF(B2:AF2,"L")). For 18 P, 10 A, and 2 L marks, this returns 20/30, or 66.67% before rounding.
Format the result as a percentage rather than multiplying the formula by 100. Select the result cells and choose Home > Number > Percent Style, then adjust the displayed decimal places if needed. Formatting changes how the result appears, not the underlying calculation. If there are no recorded marks, the denominator is zero; add a check or leave the result blank until attendance has been entered instead of treating the student as having a meaningful 0% rate.
Count attendance for a date or a roster
To count how many students were present on one date, use the column for that date and exclude its heading. With the selected date’s marks in B2:B31 and P meaning present, use =COUNTIF(B2:B31,"P"). If 24 of those 30 students have a P, the result is 24. For another date, use its column instead. This answers a different question from the row formula: it counts students for one date rather than days for one student.
For a longer-running register, a vertical log can be easier to maintain than a new block of date columns each month. In this design, each row is one student’s record for one date, column A contains the student name, and column B contains the attendance code. With records in A2:B100, the name to summarize in E2, and P meaning present, use =COUNTIFS($A$2:$A$100,E2,$B$2:$B$100,"P"). If the matching records for the name in E2 contain 19 P marks, the result is 19. COUNTIFS applies both conditions: the name must match E2 and the status must match P.
A vertical log is useful when dates are added frequently, but it requires a date column and carefully entered records. The simple monthly grid is often easier to read at a glance. Whichever layout you choose, avoid counting the same student-date combination twice; duplicate records inflate totals.
Common counting errors and how to fix them
- The formula returns zero: Check that the criterion matches the actual cell contents. A formula looking for P will not count cells containing Present or a different symbol. Also check for spaces, such as P followed by a space.
- The total is too high: Confirm the range covers only the intended student and dates. Including a totals column, another month, or a second session may count more marks than expected.
- Future days count as absences: COUNTIF does not count blanks as A, but a manually entered A in a future date will be counted. Leave unheld or future sessions blank, or use a separate code and define how it affects the percentage.
- The percentage looks wrong: Check whether the denominator includes the correct statuses and whether late marks count as present under your reporting rule. Also check that blanks and cancelled sessions are not being treated as held sessions.
- The result changes when copied: A copied formula should usually adjust its row references. If a reference was locked with dollar signs, it may keep counting the original row. Conversely, in the vertical log example, the dollar signs intentionally keep the data ranges fixed as the summary formula is copied.
For auditing, compare a few calculated totals with a manual count and inspect the range borders in the formula bar. A formula can be syntactically correct while answering the wrong question because the range, code, or denominator does not match the workbook’s attendance rules.
Choose a layout and reporting rule that fit
A grid with one student per row is usually the clearest option for a month or term with a fixed set of dates. It makes a student’s attendance easy to scan and lets a simple COUNTIF produce a total. A vertical list is often better for ongoing data entry, filtering by date, or combining records from several terms. It also makes it easier to add fields such as class, session, or reason, although the summary formulas then need to use the appropriate conditions.
Whichever structure you use, agree on what counts as attendance before publishing percentages. A full-day absence, a late arrival, an excused absence, a school closure, and an unrecorded mark are not interchangeable. Excel will faithfully count the codes you provide, but it cannot infer a school’s policy. Keep the raw marks in the sheet and put summaries in separate columns or a separate summary area so that readers can trace a total back to the underlying records.
Frequently Asked Questions
What is the basic Excel formula to count attendance?
Use COUNTIF on the cells containing the student’s marks. For example, if the marks are in B2:AF2 and P means present, use =COUNTIF(B2:AF2,"P").
Can Excel count the word Present instead of P?
Yes. Set the criterion to the exact word used in the cells. Keep the code and spelling consistent, because a formula looking for Present will not count a different label such as P or Present – late.
How do I count total absences?
Use COUNTIF on the attendance range with the absence code as its criterion. If A marks an absence and the student’s marks are in B2:AF2, =COUNTIF(B2:AF2,"A") counts the A entries.
Should blank cells count as absences?
Usually not. A blank may mean that attendance has not been entered, the date is in the future, or the session did not take place. Use an explicit absence code for a recorded absence and decide separately how to handle unrecorded or cancelled sessions.
Why does my attendance percentage differ from the number of days present?
A count is a number of marks; a percentage divides attended sessions by a chosen total. The percentage can differ depending on whether late arrivals count as attended and whether blanks, closures, or other codes belong in the denominator. Define those rules before comparing rates.
Related
- Excel Export to CSV with Semicolons
- Excel Export CSV Comma or Semicolon? Change the Delimiter
- Excel Export to CSV: Options and What They Mean
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Find Greater Than in Excel: Formulas, Filters, and RulesTo find values greater than a number in Excel, use the greater-than operator > in a formula, or…
- Combine Day, Month, and Year in Excel with a Date FormulaTo combine a day, month, and year from separate cells into a real Excel date, use the DATE…
- Excel Sum Positive Values Only: Formulas and ExamplesTo sum only positive values in Excel, use SUMIF with the criterion ">0". If the numbers are in…
- BMI Formula for Excel: Metric and ImperialFor a metric BMI calculation in Excel, put weight in kilograms in B2 and height in meters in…
- Excel Formula for File Path: Get the Workbook LocationIn Excel for Microsoft 365 on Windows, use =CELL("filename",A1) to return the saved workbook’s path, file name, and…
- Excel Page Break Formula: What Works and What Doesn’tNo Excel worksheet formula can insert a print page break or tell Excel exactly where a printed page…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.