Attendance Present Absent Formula in Excel: Easy Setup

Formulas & functions · Updated November 21, 2026

To count attendance marks in Excel, use =COUNTIF(B2:U2,"P") for the number present and =COUNTIF(B2:U2,"A") for the number absent, assuming one student’s daily marks are in B2:U2. Enter P for present and A for absent; COUNTIF counts each code in the selected range.

Set up a simple attendance sheet

A clear layout makes the formulas easier to enter and check. Put student names in column A, one class date in each column from B to U, and a P or A in the cell where a student’s row and a date’s column meet. Keep the first row for headings and the first student on row 2. For example, A1 can contain Student, B1 the first class date, and so on through U1.

Reserve columns to the right of the date columns for totals. In this example, put Present in V1, Absent in W1, and Attendance % in X1. The range B2:U2 then covers 20 possible class sessions for the student in row 2. If your term has more or fewer sessions, adjust the range to include exactly the date columns you use.

  • Use consistent codes throughout the sheet: P for present and A for absent.
  • Leave a cell blank when that session has not happened yet or the mark is not known.
  • Use one row per student and one column per session so each mark has a clear location.

Excel formulas use commas between arguments in the examples below. Depending on regional settings, Excel may display or require semicolons instead. The formula logic is the same.

Count present and absent marks with COUNTIF

COUNTIF checks a range for cells that meet one condition. For the example layout, with dates in B1:U1, marks in B2:U2, and the present total in V2, enter this formula in V2:

With the student’s attendance marks in B2:U2, use this formula to count P entries:

=COUNTIF(B2:U2,"P")

With the same marks in B2:U2 and the absent total in W2, enter this formula in W2:

=COUNTIF(B2:U2,"A")

These formulas count only cells that match the specified letter. They do not count blanks or other codes such as L for late. Copy both formulas down their columns for additional students: select the formula cell and drag its fill handle down, or copy and paste it into the rows below. Excel adjusts the row references automatically, so the next student’s formula checks B3:U3.

Make sure the marks match the codes exactly. COUNTIF is not case-sensitive, so p is counted as P, but a longer entry such as Present is not. If you choose words instead of letters, change the criterion in the formula to match the entry you use.

Worked example: check the totals

Suppose a student has 17 P marks, 2 A marks, and one blank cell across 20 scheduled sessions. With the student’s entries in B2:U2, the present formula returns 17 and the absent formula returns 2. The blank is ignored; it is not treated as either attendance or absence. This makes it possible to leave future dates empty without inflating either total.

For a quick manual check, count the marks in that row and compare them with the totals. Here, 17 present plus 2 absent equals 19 recorded outcomes, while the schedule contains 20 dates. The one-session difference is the blank cell. That difference can be useful for spotting an unrecorded mark, but it could also simply represent a class that has not taken place yet.

If your totals seem wrong, check the cell range before changing the formula. A range that stops at T2 will miss the final date in U2; a range that includes a notes column could count an unintended code. The range should cover the attendance entries only.

Calculate an attendance percentage

For a percentage based only on recorded P and A marks, divide the present count by the total number of recorded outcomes. With attendance marks in B2:U2 and the percentage result in X2, enter:

=IFERROR(COUNTIF(B2:U2,"P")/(COUNTIF(B2:U2,"P")+COUNTIF(B2:U2,"A")),0)

In the example with 17 present marks and 2 absences, the calculation is 17 ÷ (17 + 2), or about 89.47%. The blank session does not enter the denominator. Format X2 as a percentage using Home > Number > Percent Style, then choose the number of decimal places you want. The stored value is approximately 0.8947; percentage formatting displays it as 89.47%.

IFERROR prevents a division error when there are no recorded P or A marks; this formula displays 0 in that case. If you would rather show a blank until the first mark is entered, use the following version instead. With the same B2:U2 layout and the result in X2, enter:

=IF(COUNTIF(B2:U2,"P")+COUNTIF(B2:U2,"A")=0,"",COUNTIF(B2:U2,"P")/(COUNTIF(B2:U2,"P")+COUNTIF(B2:U2,"A")))

That formula returns an empty display when there are no recorded attendance marks, and calculates the percentage once a P or A is present. It still treats a blank as unrecorded, not absent. Decide which behavior is more useful for your reporting before copying the formula through the sheet.

Handle blanks, late arrivals, and other codes

A blank cell can mean different things: a future class, an attendance mark that has not been entered, or a missed record. Excel cannot tell which meaning you intended. Keep the date headings visible and use a separate review step to check past sessions with blank marks. Do not automatically count every blank as absent unless that is the policy you mean to apply.

For late arrivals, choose a consistent rule. If L means late and your policy treats late students as present for attendance-rate purposes, the percentage formula needs to include L in its numerator and denominator. With marks in B2:U2, use:

=IFERROR((COUNTIF(B2:U2,"P")+COUNTIF(B2:U2,"L"))/(COUNTIF(B2:U2,"P")+COUNTIF(B2:U2,"L")+COUNTIF(B2:U2,"A")),0)

This counts L as present for the rate, but the separate present-total formula still counts only P. That distinction can be useful if you want to report late arrivals separately rather than combine them with on-time attendance. If your rules count a late arrival differently, change the calculation to match the rule and label the result clearly.

You can make entries more consistent with a drop-down list. Select the attendance cells, then choose Data > Data Validation. On the Settings tab, choose List under Allow and enter P,A as the source. A list helps reduce accidental entries such as “Absent” or a space after a letter. If you use additional codes, include them in the list and make sure the totals and percentage formulas handle each one intentionally.

Count attendance by date instead of by student

The same COUNTIF method works vertically. A daily summary can show how many students were present or absent on a particular date. Suppose student marks for the first session are in B2:B31, with one student per row. To count P entries for that date, enter:

With the first date’s student marks in B2:B31 and the daily present total in B32, use:

=COUNTIF(B2:B31,"P")

With those same marks in B2:B31 and the daily absent total in B33, use:

=COUNTIF(B2:B31,"A")

Copy the summary formulas across the date columns to see totals for other sessions. This layout answers a different question from the per-student totals: it summarizes a class meeting rather than an individual student’s record. If the roster changes, make sure the selected rows include all current students and do not accidentally include the summary rows themselves.

Keep the workbook easy to maintain

For a small class, a grid with one row per student is often easy to read. For a long-running register or a sheet that needs filtering by date, consider storing each attendance event as one row in a separate log: student, date, and status. A log is less compact, but it is easier to sort, filter, or summarize in different ways. The simple row-based formulas in this guide assume the grid layout; a log may need different ranges or a PivotTable to produce summaries.

Regardless of layout, protect the meaning of each code with clear headings or a small legend. Keep totals separate from the cells where people enter marks, and avoid typing over formula cells. You can also freeze the heading row using View > Freeze Panes so student names and dates remain visible while scrolling. On Windows, Ctrl+Z undoes ordinary edits; on Mac, use Command+Z. Undo is not a substitute for keeping a backup of an important register.

Common formula problems

  • The total is zero: Confirm that the formula points to the cells containing the marks and that the criterion matches the code. COUNTIF ignores entries that do not match P or A.
  • The total is too low: Check whether the range includes every date column. Also look for marks stored in a different location or cells with unexpected text.
  • The percentage looks like 0.89: The calculation may be correct, but the cell is formatted as a decimal. Apply Percentage formatting to display about 89%.
  • The percentage is unexpectedly low: Check whether your denominator includes only the statuses that should count. A formula based on all scheduled sessions has different results from one that excludes blanks.
  • Excel rejects a formula: Check that parentheses and quotation marks are balanced. Some regional settings use semicolons between arguments rather than commas.

A frequent misconception is that an empty attendance cell automatically means absent. COUNTIF does not make that assumption: it counts the values you ask it to count. This is usually safer because an empty cell may refer to a future session or an incomplete record. If a reporting rule requires blanks to count as absences, first decide whether that is appropriate for the data, then use a calculation designed for that rule rather than relying on a simple P-versus-A percentage.

Frequently Asked Questions

What formula counts present students in Excel?

Use COUNTIF with the range containing the attendance marks and P as the criterion. For example, if a student’s marks are in B2:U2, =COUNTIF(B2:U2,"P") returns the number of present marks in that row.

How do I count absences in Excel?

Use the same range with A as the criterion. If the marks are in B2:U2, =COUNTIF(B2:U2,"A") counts the absences. Copy the formula down for other students, checking that the range shifts to the correct row.

How do I calculate attendance percentage if some cells are blank?

Divide the number of P marks by the combined number of P and A marks. That treats blanks as unrecorded and excludes them from the denominator. With 17 P marks and 2 A marks, the result is 17/19, or about 89.47%.

Can I use Present and Absent instead of P and A?

Yes. Enter the full words consistently and replace the formula criteria with the exact words, such as =COUNTIF(B2:U2,"Present"). Short codes are quicker to enter, while full words can be easier to read; a drop-down list can help keep either system consistent.

Can the same formula count attendance for a whole class on one date?

Yes. Select the vertical range of marks for that date instead of one student’s horizontal row. For example, =COUNTIF(B2:B31,"P") counts present marks in that day’s column for students in rows 2 through 31.

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.