COUNTIF Formula for Attendance in Excel

Formulas & functions · Updated November 22, 2026

To count attendance marks in Excel, use =COUNTIF(B2:U2,"P"). If a student’s 20 session entries are in B2:U2 and “P” means present, the formula returns the number of sessions marked present; for example, 18 P entries produce a result of 18.

Set Up an Attendance Sheet

A simple attendance register usually has one row per student and one column per class session. Put student names in column A, session dates in columns B through U, and short, consistent codes in the grid. For example, use P for present, A for absent, and L for late. A blank cell can mean attendance has not yet been recorded, but it should not also mean absence.

In this layout, row 1 can hold the dates, while rows 2 through 31 hold 30 students. Each student’s attendance marks then occupy one row, such as B2:U2 for the first student. This arrangement makes it easy to count one student’s marks across sessions or to count a whole class’s marks for one date down a column.

Before entering data, decide what each code means. If late arrivals count as present for your reporting rules, decide whether to include L in the present total or show it separately. A count is only as meaningful as the rules used to enter the marks.

How COUNTIF Works

COUNTIF checks a range of cells against one condition and returns how many cells satisfy it. Its two arguments are the range to inspect and the criterion to match. In an attendance sheet, the range is normally one student’s session cells, and the criterion is the mark to count.

With the first student’s attendance marks in B2:U2 and P used for present, =COUNTIF(B2:U2,"P") counts cells whose value is P. COUNTIF is not case-sensitive, so it treats p and P as the same criterion. It does not, however, treat a blank cell or an A mark as P.

To count absences for that same student in B2:U2, use =COUNTIF(B2:U2,"A"). If the row contains 18 P marks, one A, and one L, this formula returns 1. You can put the totals in separate columns, such as V for present and W for absent, then fill the formulas down for other student rows.

Count Attendance by Student or by Date

Counting across a row answers, “How many sessions did this student attend?” Counting down a column answers, “How many students were present on this date?” The formula pattern is the same; only the range changes.

For the first session, if the 30 students’ marks are in B2:B31 and 24 cells contain P, enter =COUNTIF(B2:B31,"P") in a summary cell. It returns 24. For the next session, use C2:C31, and continue across the date columns. The summary row can show daily attendance totals alongside the dates.

For a month with many class dates, you can keep the daily totals in a row beneath the register or on a summary sheet. Use clear labels such as “Present” and “Absent,” and check that every summary range covers the same student rows. If a new student is added below row 31, extend the ranges or convert the register into an Excel table so the data structure can expand more conveniently.

Calculate an Attendance Percentage

A present count is not the same as an attendance percentage. To calculate a rate, divide the number of present sessions by the number of sessions for which a mark has been recorded. For a student with 18 P marks, one A, and one L in B2:U2, there are 20 marked sessions. If only P counts as present, the rate is 18 ÷ 20, or 90%.

With those marks in B2:U2 and every completed session recorded as P, A, or L, use =IFERROR(COUNTIF(B2:U2,"P")/COUNTA(B2:U2),0). It returns 0.9 for the example, which Excel displays as 90% after you format the result as a percentage. On the Home tab, use the Percent Style button in the Number group, or choose a percentage format from the number-format options.

COUNTA counts nonblank cells, so this approach works when each recorded session has exactly one mark and cells for future or unrecorded sessions remain blank. It can give a misleading denominator if someone types notes in the attendance range or if a cell contains a formula returning an empty string. For a fixed reporting period, another option is to divide by the known number of scheduled sessions, but exclude holidays and non-class days from that number.

Attendance policies differ on lateness. If L should count as attended, the numerator needs to include both P and L. With the same B2:U2 layout and P, A, and L as the only recorded marks, use =IFERROR((COUNTIF(B2:U2,"P")+COUNTIF(B2:U2,"L"))/COUNTA(B2:U2),0). For the example, that is 19 ÷ 20, or 95%. Choose the rule that matches your organization’s policy and label the result clearly.

Choose Consistent Marks and Prevent Entry Errors

COUNTIF looks for the criterion you specify. A cell containing “Present” will not be counted by a formula looking for P, and a mark with an extra space, such as “P ”, may not match the intended code. Excel ignores letter case for this comparison, but consistent spelling and spacing still matter.

A dropdown list can help keep entries consistent. Select the attendance cells, then choose Data > Data Validation. In the dialog, set Allow to List and enter the permitted marks as the Source, for example P,A,L. List separators can depend on regional settings, so use the separator Excel expects on your computer. A dropdown does not prevent every mistake, but it reduces accidental variations and makes later counting more reliable.

Other practical checks include:

  • Keep status codes in their own cells rather than mixing a code with notes such as “P – arrived early.”
  • Use the same code for the same outcome throughout the register.
  • Keep scheduled dates in headers so you can distinguish a class session from a holiday or an unplanned cancellation.
  • Review blanks before reporting totals; a blank may mean “not entered,” not “absent.”

COUNTIF Compared with Related Options

COUNTIF is a good fit when you want to count one status in one range, such as P marks for a student or A marks for a class on one date. If you need to count entries that satisfy multiple conditions at once, such as absences for a particular group on a particular date, COUNTIFS is the related function designed for multiple criteria. It is not necessary for a basic attendance total.

COUNTA answers a different question: how many cells are nonblank? It can help identify how many attendance records have been entered, but it does not identify which entries mean present. COUNTIF is also different from simply counting every cell in a range, because blank cells and other status codes do not meet a criterion of P.

For a small register, formulas beside the attendance grid are often enough. For larger records, filters, Excel tables, or a separate summary sheet can make data easier to review. Whatever method you choose, keep the underlying entries consistent; a summary cannot reliably correct inconsistent source data.

Common COUNTIF Problems

If the result is zero when you expect a positive count, first check that the formula points to the correct row or date column. Then compare the criterion with the actual cell values. A formula searching for “P” will not count “Present,” and a range that stops at row 30 will miss entries in row 31.

If a total seems too high, look for stray values that match the criterion, duplicated records, or a range that includes another section of the worksheet. COUNTIF is case-insensitive, so changing p to P will not fix a mismatch caused by some other character. Check for leading or trailing spaces and make sure marks have not been combined with comments.

Also confirm that your workbook’s formula separator matches its regional settings. The examples here use commas between arguments, as requested for standard Excel syntax; some regional configurations use semicolons when entering formulas. If Excel rejects a formula, its error message or automatic correction may indicate that the local separator differs. On Windows, these instructions refer to Excel for Microsoft 365. Excel for Mac and Excel for the web have the same COUNTIF function; ribbon placement and dialog appearance can vary slightly, but the counting logic does not change.

Frequently Asked Questions

How do I count present students in Excel?
If each student’s mark for a date is in B2:B31 and P means present, use =COUNTIF(B2:B31,"P"). The result is the number of students marked P in that session.

Can COUNTIF count absences as well as attendance?
Yes. Use A as the criterion to count absence marks. The selected criterion must match the code used in the register; COUNTIF will not interpret a blank as an absence unless your data and formula explicitly treat it that way.

Does COUNTIF distinguish between P and p?
No. COUNTIF text comparisons are not case-sensitive, so P and p match the same criterion. Spaces and different words, however, can produce different results.

How do I calculate attendance percentage when some dates are blank?
Divide the present count by the number of recorded sessions, not by every possible date, if blank means “not recorded yet.” A COUNTIF-over-COUNTA calculation can do this when all recorded cells contain a valid attendance mark and no unrelated text.

Should a late mark count as present?
That depends on the attendance policy. Count L separately if lateness is its own category, or include L in the numerator of the attendance-rate calculation if the policy treats late arrivals as attended. State the rule beside the reported total so readers understand it.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.