Attendance Calculation Formula in Excel With Examples

Formulas & functions · Updated November 21, 2026

To calculate attendance in Excel, divide the number of sessions marked present by the number of sessions counted under your attendance policy. If a student’s attendance marks are in B2:AF2, with “P” for present and “A” for absent, use =IFERROR(COUNTIF(B2:AF2,"P")/(COUNTIF(B2:AF2,"P")+COUNTIF(B2:AF2,"A")),0) and format the result as a percentage. This version ignores blank cells and excludes any other codes, such as excused absences, from the denominator.

What the Attendance Percentage Means

An attendance percentage compares attendance credited to a person with the sessions included in the calculation. The exact definition depends on the rules you need to follow: some schools or workplaces count every scheduled session, while others exclude excused absences, holidays, or sessions a person was not expected to attend. Decide what counts before choosing a formula; Excel can calculate the rate, but it cannot determine the policy.

For the formula above, the assumed layout is one person per row, with attendance codes across B2:AF2. “P” means present and “A” means absent. If the row contains P, P, A, P, and A, Excel counts three present sessions and two absent sessions. The result is 3 ÷ 5, or 60%. Empty cells do not count as either present or absent, so they are left out of the calculation.

That distinction matters when you have not entered every session yet. If blank cells mean “not recorded,” ignoring them is usually useful. If a blank is supposed to mean an absence, however, the formula will overstate attendance until you enter an A. Consistent data entry is as important as the formula itself.

Set Up the Attendance Sheet

A simple layout makes formulas easier to read and copy. Put each student, employee, or participant on a separate row. Put the scheduled sessions in columns, and enter a consistent code in the cell where a person’s row and a session’s column meet. For example, row 1 can contain session dates and row 2 can contain one person’s marks.

Before entering records, write down what each code means. A practical scheme might use P for present, A for absent, and E for excused. Avoid switching between values such as “Present,” “P,” a check mark, and a blank: Excel treats these as different cell contents. If you want full words instead of short codes, adapt the formula’s text criteria to match them exactly.

To reduce typing mistakes in Microsoft Excel for Microsoft 365 on Windows, you can make a drop-down list. Select the cells where attendance codes will go, then choose Data > Data Validation. In the dialog, set Allow to List and enter the permitted codes as the source, separated by commas. On Excel for Mac, the Data Validation command is also on the Data tab, although the dialog’s appearance can differ. Excel for the web supports data validation, but some editing options may be presented differently from the desktop app.

Build and Format the Basic Formula

  1. Enter the same code for each kind of outcome, such as P for present and A for absent.
  2. Choose the result cell for the person’s attendance rate, such as AG2 if attendance marks run from B2 through AF2.
  3. Enter the formula from the opening paragraph, changing the range if your attendance columns differ.
  4. Copy the formula down for other people. Check that the row references change for each person.
  5. Select the result cells and choose Home > Number > Percent Style. Increase or decrease the displayed decimal places as needed.

The formula returns a fraction internally: 0.6 represents 60%. Applying percentage formatting changes how that value is displayed; it does not change the underlying calculation. You can also use the Windows shortcut Ctrl+Shift+% to apply Percentage format. On a Mac, use Control+Shift+% if that shortcut is available with your keyboard layout; the Home tab’s number-format controls are a dependable alternative.

In the example, the formula counts the P cells in B2:AF2, then divides by the sum of the P and A counts in that same range. IFERROR returns 0 if the division has no valid denominator, such as when there are no P or A marks. A zero can look like a real 0% attendance rate, though, so for a report you may prefer a blank or a label when there are no recorded sessions. One option for the same B2:AF2 layout is =IF(COUNTIF(B2:AF2,"P")+COUNTIF(B2:AF2,"A")=0,"",COUNTIF(B2:AF2,"P")/(COUNTIF(B2:AF2,"P")+COUNTIF(B2:AF2,"A"))). It displays an empty result until at least one P or A is entered.

Decide How to Treat Excused Absences

There is no universal Excel rule for excused absences. If E means an excused absence that should not count as a session attended or missed, exclude it from both the numerator and denominator. The basic P-and-A formula already does this, as long as E is not entered as P or A.

If your policy says an excused absence still belongs in the denominator, include E in the denominator but not the numerator. With P, A, and E codes in B2:AF2, use =IFERROR(COUNTIF(B2:AF2,"P")/(COUNTIF(B2:AF2,"P")+COUNTIF(B2:AF2,"A")+COUNTIF(B2:AF2,"E")),0). If a row contains four P marks, one A, and one E, this version returns 4 ÷ 6, or about 66.67%. The version that excludes E returns 4 ÷ 5, or 80%.

Make the policy visible near the results, especially if the spreadsheet will be shared. A column heading such as “Attendance rate (excused absences excluded)” can prevent readers from comparing percentages calculated under different rules. For formal reporting, follow the organization’s published definition rather than choosing the more favorable result.

Calculate Attendance from a Session Log

A wide grid is convenient for a small class or team, but a session log can be easier to maintain when records are added over time. In a log, put a person’s name in column A, the session date in column B, and the attendance code in column C. Put the name to summarize in E2. With records in rows 2 through 100, this formula counts that person’s P and A entries and calculates their rate: =IFERROR(COUNTIFS($A$2:$A$100,E2,$C$2:$C$100,"P")/(COUNTIFS($A$2:$A$100,E2,$C$2:$C$100,"P")+COUNTIFS($A$2:$A$100,E2,$C$2:$C$100,"A")),0).

COUNTIFS applies more than one condition: here, the row must match the name in E2 and the status in the relevant part of the formula. The dollar signs keep the log ranges fixed when you copy the formula to another summary row, while E2 changes to E3, E4, and so on. This approach is useful when each new session is appended as another record rather than added as a new column. As with the grid formula, blanks and codes other than P or A are not included.

Calculate a Group-Wide Rate

A group-wide attendance rate is not always the same as the average of everyone’s individual percentages. If you want the rate across all recorded person-session outcomes, divide the total number of P marks by the total number of P and A marks. For attendance marks covering B2:AF30, use =IFERROR(COUNTIF(B2:AF30,"P")/(COUNTIF(B2:AF30,"P")+COUNTIF(B2:AF30,"A")),0). This weights each recorded session equally, even if some individuals have more recorded sessions than others.

For example, suppose one person has 9 attended sessions out of 10, and another has 1 out of 2. The average of their individual percentages is (90% + 50%) ÷ 2, or 70%. The group-wide rate is 10 attended sessions out of 12 total, or about 83.33%. Neither calculation is automatically wrong: they answer different questions. Use the average when you want each person’s rate to have equal weight; use the combined rate when you want each session outcome to have equal weight.

Common Errors and Useful Checks

If the result is unexpectedly low, high, or zero, check the source marks before rewriting the formula. COUNTIF looks for text that matches the criterion, so a cell containing “Present” will not be counted by a formula looking for “P.” A trailing space, a different abbreviation, or an accidental number can also make entries inconsistent.

  • Check the range: Confirm that the formula includes every relevant attendance column or log row, and does not include unrelated notes.
  • Check the denominator: Confirm whether excused absences, late arrivals, canceled sessions, or unrecorded dates belong in your policy’s total.
  • Check blanks: Decide whether an empty cell means not yet entered, not applicable, or absent. The basic formula treats it as not counted.
  • Check copied formulas: When copying down, the person’s attendance row should change, while fixed reference ranges in a log should remain fixed.
  • Check the display: A result such as 0.8 is 80% when formatted as a percentage. Do not multiply by 100 and then also apply percentage formatting, or it will display incorrectly.

Also check for policy edge cases. If someone joins after sessions have started, dates before their start date should generally not be treated as absences unless the relevant rules say otherwise. If a session is canceled, remove it from the expected attendance total or mark it in a way your formula explicitly handles. A formula that counts codes cannot infer these circumstances from the calendar by itself.

When to Use a Different Method

Use the row-based COUNTIF approach when each person has one row and attendance marks are spread across session columns. Use a log and COUNTIFS when records are stored one per row, when you expect the list to grow, or when you need to summarize by person and status. For larger reporting tasks, Excel PivotTables can group a log by name and status, but you still need to define which codes count in the rate and calculate the percentage accordingly.

For a very small record set, counting manually may be enough, but a formula is easier to update and audit. Avoid replacing the formula with a hand-entered percentage if attendance marks will change: the displayed result will then become stale. Keep raw marks separate from calculated results, and label the percentage column with the policy it uses.

Frequently Asked Questions

How do I calculate attendance percentage in Excel?
Count the sessions marked present and divide by the sessions included in the denominator. For P and A codes in B2:AF2, use =IFERROR(COUNTIF(B2:AF2,"P")/(COUNTIF(B2:AF2,"P")+COUNTIF(B2:AF2,"A")),0), then format the result as a percentage.

How do I calculate attendance if I use Present and Absent instead of P and A?
Use the exact text stored in your cells as the COUNTIF criteria. If the marks are “Present” and “Absent” in B2:AF2, use =IFERROR(COUNTIF(B2:AF2,"Present")/(COUNTIF(B2:AF2,"Present")+COUNTIF(B2:AF2,"Absent")),0). Keep spelling and spacing consistent.

Should excused absences count in the attendance denominator?
That depends on the attendance policy. Exclude the E code from the denominator when an excused absence does not count as a missed eligible session; include it when the policy counts all scheduled sessions. Use one rule consistently and state it alongside the reported rate.

Why does my attendance formula show 0%?
Check that the cells contain the exact status text used by the formula and that the selected range includes those cells. If there are no matching P or A entries, the IFERROR version returns zero; use a blank-result version if you want to distinguish “no records” from actual 0% attendance.

Can I calculate attendance for the whole class?
Yes. Apply the P-over-P-and-A calculation to the full attendance grid to get the combined rate. That result weights each session mark equally; averaging individual percentages weights each person equally and may produce a different answer.

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.