Attendance Calculation in Excel: Formulas and Examples

Formulas & functions · Updated November 21, 2026

To calculate attendance in Excel, divide the number of sessions marked present by the number of sessions with a recorded present or absent status. If a person’s attendance marks are in B2:F2, with P for present, A for absent, and blanks for sessions not yet recorded, use =IF(COUNTIF(B2:F2,"P")+COUNTIF(B2:F2,"A")=0,"",COUNTIF(B2:F2,"P")/(COUNTIF(B2:F2,"P")+COUNTIF(B2:F2,"A"))) and format the result as a percentage. Four P marks and one A mark return 80%.

How an attendance sheet is organized

A simple attendance worksheet uses one row per person and one column per class, shift, meeting, or workday. Put names in column A, dates in row 1, and a short code in each date cell. For example, B1:F1 might contain five meeting dates, while B2:F2 contains the first person’s attendance records. This layout makes it easy to compare people and add new sessions in columns.

Use consistent codes rather than mixing words, symbols, and numbers. A basic scheme is P for present and A for absent. A blank cell should mean something specific, such as a future session or a session not yet entered—not an absence. If the session was cancelled, consider marking it separately or excluding that date from each person’s denominator.

Before entering records, decide what the percentage is meant to describe. A weekly class rate, a full-term rate, and a rate for scheduled work shifts can use the same arithmetic, but they may include different sessions. A clear definition prevents a correct formula from producing a misleading result.

Calculate attendance with COUNTIF

COUNTIF counts cells that meet a condition. With P and A as the only recorded attendance codes, count the P entries for the numerator and count both P and A entries for the denominator. The formula in the opening example therefore ignores blank cells, which is useful when future dates have already been added to the sheet.

In the example, the person attended four recorded sessions and missed one: 4 divided by 5 equals 0.8, or 80%. Excel stores that result as a decimal. To display it as 80%, select the result cell and choose Home > Number > Percent Style, or use the percentage button in the Number group. Increase or decrease decimal places if the report needs more or less precision.

For a quick calculation without an error-handling check, the same P/A layout can use =COUNTIF(B2:F2,"P")/(COUNTIF(B2:F2,"P")+COUNTIF(B2:F2,"A")). It returns a division error if there are no recorded P or A marks, so the IF version is safer for rows that may be empty. The IF version leaves the result blank until at least one applicable attendance mark has been entered.

Build a reusable attendance table

For a roster with many people, create columns for the person’s name, session dates, and summary measures. For example, with names in A2:A30 and attendance codes for the first person in B2:K2, use column L for sessions recorded, M for present sessions, N for absences, and O for the attendance rate. Copy the formulas down the rows for the other people.

  • With codes in B2:K2, enter =COUNTIF(B2:K2,"P") in M2 to count present sessions. Four P marks return 4.
  • With the same layout, enter =COUNTIF(B2:K2,"A") in N2 to count absences. One A mark returns 1.
  • With P and A as the only counted codes, enter =M2+N2 in L2 to calculate recorded sessions. Four present marks and one absence return 5.
  • With present count in M2 and recorded-session count in L2, enter =IF(L2=0,"",M2/L2) in O2. The example returns 0.8, displayed as 80% when O2 is formatted as a percentage.

Keeping counts and rates in separate columns makes a result easier to audit. If a manager asks why someone has an 80% rate, the sheet shows both the four attended sessions and the five recorded sessions rather than only the final percentage. After entering formulas in the first row, select the formula cells and drag the fill handle down, or copy and paste them into the remaining rows.

Handle late arrivals, excused absences, and other codes

Attendance policies differ on whether a late arrival counts as present, partly present, or absent. Choose the rule before writing the formula and document it near the table. If L means late and late arrivals count as attended sessions, include L in both the numerator and denominator. With codes in B2:F2, use =IF(COUNTIF(B2:F2,"P")+COUNTIF(B2:F2,"L")+COUNTIF(B2:F2,"A")=0,"",(COUNTIF(B2:F2,"P")+COUNTIF(B2:F2,"L"))/(COUNTIF(B2:F2,"P")+COUNTIF(B2:F2,"L")+COUNTIF(B2:F2,"A"))). Three P marks, one L, and one A return 80% if late counts as attended.

An excused absence is not automatically the same thing as a session that did not happen. If E means excused absence and policy excludes it from the attendance rate, leave E out of both numerator and denominator. If the policy counts excused absences as missed sessions, include E in the denominator but not the numerator. The formula must reflect the rule; Excel cannot decide what a code means.

Do not use a formula that counts every nonblank cell as a session if the cells may contain notes such as “holiday,” “cancelled,” or “permission.” Such entries can accidentally increase the denominator. Counting only the recognized attendance codes is more reliable, particularly when several statuses are used.

Enter codes consistently and prevent mistakes

A formula can only count the values it is told to count. A cell containing “Present,” a lowercase p, or a trailing space may not match the intended P code in a formula that expects P. Excel’s COUNTIF is generally not case-sensitive, but different words and extra spaces still create inconsistent data. A dropdown list can reduce these entry errors.

In Excel for Microsoft 365 on Windows, select the cells where attendance codes will go, then choose Data > Data Validation. In the Data Validation dialog, set Allow to List and enter the permitted codes in Source, separated by commas, such as P,A,L,E. Confirm that the in-cell dropdown option is enabled, then select OK. On Mac, use Data > Data Validation; dialog labels and placement can vary slightly by Excel release. Excel for the web supports data validation, though available controls may differ from the Windows desktop dialog.

You can also use conditional formatting to make codes easier to scan. Select the attendance grid and choose Home > Conditional Formatting > Highlight Cells Rules > Text that Contains, then create a rule for a code such as A. Choose a visible format and repeat for other codes as needed. Formatting is a visual aid, not a substitute for correct codes or a correct formula.

Choose the right denominator

The denominator is the number of sessions the person was expected to attend and that your policy says should count. This is the most common source of disagreement in attendance reports. If every person is expected at every class, a shared set of dated columns works well. If people have different schedules, do not treat an unassigned day as an absence.

For example, if an employee works Monday, Wednesday, and Friday, a Tuesday column should not reduce that employee’s attendance rate. One solution is to keep a separate schedule or use a per-person record that includes only assigned sessions. Another is to add a code such as N for not scheduled and exclude N from both numerator and denominator. Whichever approach you choose, state the rule so readers understand what the percentage represents.

Cancelled sessions also need a deliberate treatment. If a class is cancelled for everyone, removing that date’s column or using a cancellation code excluded from the denominator can keep the rate meaningful. If sessions are cancelled for only one person, the policy may treat that differently. Avoid silently changing historical records; retain a note or an audit trail when the reason for an adjustment matters.

Check and troubleshoot the result

If a rate looks wrong, check the raw marks before rewriting the formula. Confirm that the selected range covers the intended dates, that the formula is on the correct row, and that every relevant value uses the agreed code. A copied formula should adjust its row references—for example, B2:K2 becomes B3:K3—while keeping the same columns.

  • A result of 0% can mean every recorded session was an absence, or it can mean the formula is counting a different code than the one entered.
  • A blank result from the IF formula means there are no counted sessions yet. It is not the same as zero attendance.
  • A division error usually indicates that the denominator is zero and the formula lacks an empty-row check.
  • A rate above 100% suggests the numerator and denominator use inconsistent rules or include overlapping counts.
  • If a percentage appears as 0.8 rather than 80%, apply percentage formatting; the underlying calculation may already be correct.

It is useful to test a formula with a small known example: four P entries and one A should produce 80%; five P entries should produce 100%; and an entirely blank row should remain blank if the formula is designed to do so. These checks help distinguish an arithmetic issue from an entry or policy issue.

Attendance percentage versus days present

A count and a rate answer different questions. “How many sessions did this person attend?” is answered by counting P marks. “What share of applicable sessions did the person attend?” requires dividing that count by the number of applicable recorded sessions. A report may need both, because 80% based on five sessions represents a different amount of attendance than 80% based on fifty.

Attendance rates also do not automatically measure hours attended. If a person attends only part of a long shift, a simple P/L/A code treats the whole session according to the chosen status rule. To calculate an hours-based rate, record scheduled hours and attended hours in numeric columns and divide attended hours by scheduled hours, while defining how breaks, partial shifts, and approved leave are handled. Do not mix hours and session counts in the same percentage.

Frequently Asked Questions

How do I calculate attendance percentage in Excel? Count the sessions that qualify as attended, then divide by the total sessions that should count. For P and A codes, use the formula in the opening example and format the result cell as a percentage.

How do I count the number of days present? With a person’s attendance codes in B2:F2 and P as the present code, use =COUNTIF(B2:F2,"P"). The formula returns the number of P entries, not the attendance percentage.

Should blank attendance cells count as absences? Usually, a blank is better treated as unrecorded or not yet due, while A explicitly means absent. If your policy treats blanks as absences after records are finalized, enter an absence code or adjust the formula intentionally rather than relying on ambiguous empty cells.

How can I calculate attendance when late marks count as present? Count both P and L in the numerator and count P, L, and A in the denominator, as in the late-arrival formula above. If your policy gives late arrivals a different weight, a simple session-count percentage may not represent that rule; define the calculation first.

Why does Excel show a decimal instead of a percentage? Excel stores percentages as decimals, so 0.8 is the same value as 80%. Select the result cell and apply Home > Number > Percent Style to display it as a percentage.

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.