Calculate Attendance in Excel: Formulas and Examples

Formulas & functions · Updated November 21, 2026

To calculate attendance in Excel, enter a consistent status such as P for present or A for absent in each session column, then divide the number of P entries by the number of recorded P and A entries. With session records in B2:K2, use =IFERROR(COUNTIF(B2:K2,"P")/(COUNTIF(B2:K2,"P")+COUNTIF(B2:K2,"A")),0) to calculate the attendance rate for that row; format the result as a percentage.

Set up an attendance register

A clear layout makes attendance easier to enter and formulas easier to check. Put one person on each row and one class, shift, meeting, or other scheduled session in each column. For example, use column A for names, columns B through K for ten session dates, and columns L through N for totals and the attendance rate. Put actual dates in the session headings, rather than labels such as “Day 1,” so the register remains useful when you review it later.

Keep the status codes consistent. A simple register might use P for present and A for absent. If you need to record late arrivals, excused absences, or other circumstances, decide in advance how each code affects the rate. Attendance rules differ between schools, workplaces, and organizations, so Excel cannot determine the correct policy for you.

  • Use one row per person and one column per scheduled session.
  • Keep names or identification numbers in a separate, fixed column.
  • Leave a session cell blank until attendance has actually been recorded.
  • Write down what each code means, especially if more than P and A are used.

For a new register, select the data range and choose Insert > Table. If the range has column headings, select the option indicating that the table has headers. Tables make it easier to filter and expand a list, though the basic attendance formulas work in an ordinary range too. On Windows, Ctrl+T opens the Create Table dialog; on Mac, use Command+T.

Calculate a person’s attendance rate

In the example layout, names are in A2:A31 and session statuses are in B2:K31. Row 2 contains one person’s ten-session record. The formula counts the P entries, then divides by the total of P and A entries. It deliberately leaves blank cells out of the denominator, so sessions that have not been marked do not reduce the person’s rate.

With P or A records in B2:K2, the formula =IFERROR(COUNTIF(B2:K2,"P")/(COUNTIF(B2:K2,"P")+COUNTIF(B2:K2,"A")),0) returns 0 if there are no recorded P or A entries, and otherwise returns the attendance rate. For example, if the row contains seven P entries, two A entries, and one blank, the calculation is 7 ÷ (7 + 2), or 77.78%. Select the result cell, then choose Home > Number Format > Percentage. Use the increase or decrease decimal buttons if you want more or fewer decimal places.

If showing 0% for a person with no records could be mistaken for a real result, use a blank result instead. With the same B2:K2 layout, =IF(COUNTIF(B2:K2,"P")+COUNTIF(B2:K2,"A")=0,"",COUNTIF(B2:K2,"P")/(COUNTIF(B2:K2,"P")+COUNTIF(B2:K2,"A"))) returns an empty-looking cell when there are no entries. Copy the chosen formula down the rate column for the other people. In a regular range, drag the fill handle from the formula cell down; relative references adjust for each row.

Count presents and absences

Separate totals help you inspect the data and explain how a rate was calculated. With a person’s session entries in B2:K2, =COUNTIF(B2:K2,"P") returns the number of recorded present sessions. In the same layout, =COUNTIF(B2:K2,"A") returns the number of recorded absences. Put these in separate columns, for example L2 and M2, and copy them down as needed. In the example with seven P entries and two A entries, the results are 7 and 2.

These formulas count exact matches to the specified code. If someone enters “Present” while the formula searches for P, that entry will not be counted. Either use short codes consistently or change the formula criteria to match the full words you enter. Avoid changing the definition of a code partway through a reporting period without updating the data and formulas.

Calculate attendance for a group or a session

There are two useful group-level measures, and they answer different questions. The overall session attendance rate treats each person-session as one observation. With all records in B2:K31 and only P and A included in the policy, =IFERROR(COUNTIF(B2:K31,"P")/(COUNTIF(B2:K31,"P")+COUNTIF(B2:K31,"A")),0) calculates total present records divided by total recorded P and A records. If the range contains 180 P entries and 20 A entries, the result is 180 ÷ 200, or 90%. This measure gives more weight to people with more recorded sessions.

A different calculation is the average of the individual attendance rates. That gives each person with a numeric rate equal weight, whether they attended five sessions or fifty. It can be useful for comparing people, but it is not the same as the overall session rate. If individual rates are in N2:N31, =AVERAGE(N2:N31) averages the numeric values and ignores blank cells. State which measure you are reporting; calling both simply “group attendance” can cause confusion.

To count attendance for one date, use the column for that session. If the first session’s statuses are in B2:B31, =COUNTIF(B2:B31,"P") counts how many people were marked present. If there are 28 names in the roster but only 25 P or A entries, that count is not a complete attendance rate: three records are still blank. Complete or investigate missing entries before interpreting a session total.

Handle blanks and additional status codes

A blank cell is not automatically an absence. It may mean that attendance has not been entered, the person was not scheduled, or the record is missing. The basic P/A formula counts only marked P and A cells, which is helpful for incomplete data, but it cannot distinguish a person who was not scheduled from one whose attendance has not yet been entered. Track scheduled sessions separately if that distinction matters.

For example, you might use E for excused and L for late. Decide whether a late arrival counts as present, and whether an excused session belongs in the denominator, before calculating a rate. If the policy says L counts as attended and E is excluded, the numerator and denominator need to reflect those decisions. Don’t simply include every nonblank cell: a note, typo, or excused code could then be treated as an absence even though it should not be.

For more complex rules, create a written code key and make formulas count the exact eligible codes. Test the rule on a small example where you know the expected answer. A formula is only as meaningful as the definitions behind its numerator and denominator.

Reduce entry errors with validation and formatting

A dropdown reduces spelling differences and makes the register quicker to fill in. Select the session-entry cells, then choose Data > Data Validation. In the Data Validation dialog, set Allow to List and enter the permitted codes in the Source box, separated by commas, such as P,A. Confirm that the in-cell dropdown is enabled, then select OK. The names of dialog controls can vary slightly with updates, but Data > Data Validation is the Windows Microsoft 365 ribbon path. Excel for Mac also has Data Validation on the Data tab; Excel for the web supports data validation, though the dialog layout may differ.

You can also highlight absences. Select the status range, then choose Home > Conditional Formatting > Highlight Cells Rules > Text that Contains, enter A, and choose a format. Check the result on a few cells to ensure the rule highlights the intended code and not other text. Conditional formatting changes appearance; it does not change the attendance totals or decide how a status should be counted.

Common calculation problems

  • The result is 0%: Check that the entries exactly match the formula’s criteria and that the formula points to the right row. Also check whether the row has any recorded statuses.
  • The percentage is too low: Look for blanks being counted as absences in a different denominator formula, or for codes that should be excluded under your attendance policy.
  • The rate is above 100%: Confirm that the numerator counts only statuses included in the denominator. A numerator that counts both P and L while the denominator counts only P and A can produce an inconsistent result.
  • Some entries are not counted: Check for trailing spaces, spelling differences, or inconsistent codes. A dropdown helps prevent many of these issues.
  • The rate changes unexpectedly: Confirm that the formula ranges include the intended session columns and that newly added dates are included. Table references or an updated range can help when the register grows.

Do not interpret a formula-generated number as proof that the underlying records are complete. Review missing entries and compare totals against the roster or source attendance records before sharing a report.

When to use a different layout or tool

The one-row-per-person layout works well for a small, fixed set of sessions and a straightforward P/A policy. If the register will contain many dates, multiple classes, or frequent new participants, a long-format table can be easier to maintain: use one row per person per session, with columns for person, date, and status. You can filter by date or person and build a PivotTable to summarize the records. This layout avoids adding a new column for every session, though it takes a little more setup and careful control of duplicate records.

A shared Excel workbook can suit a team that already uses Microsoft 365, but agree who edits the register and how corrections are recorded. For large organizations, recurring schedules, approval steps, or sensitive attendance records, a dedicated attendance system may offer controls and reporting that a manually maintained spreadsheet does not. Choose based on the volume of records, access requirements, and the importance of an audit trail—not just on which option produces a percentage.

Frequently Asked Questions

How do I calculate attendance percentage in Excel?
Count the present entries and divide by the total of present and absent entries. For P/A statuses in B2:K2, use =IFERROR(COUNTIF(B2:K2,"P")/(COUNTIF(B2:K2,"P")+COUNTIF(B2:K2,"A")),0), then format the result as a percentage. The formula excludes blank cells.

Should blank attendance cells count as absences?
Not unless your policy explicitly treats them that way. A blank often means the record has not been completed. The P/A formula above excludes blanks, so resolve missing entries or track scheduled sessions separately when necessary.

How do I count how many days someone was present?
With the person’s status entries in B2:K2 and P as the present code, use =COUNTIF(B2:K2,"P"). It returns the number of cells marked P, not the attendance percentage.

How do I calculate attendance for the whole class?
For a session-weighted class rate, count every P in the class range and divide by all P and A entries in that range. With records in B2:K31, use the group formula in the group section. This differs from averaging each person’s percentage.

Can I include late or excused statuses?
Yes, but decide whether each status belongs in the numerator, denominator, both, or neither. Then adjust the formula to match that written rule and test it against a small set of records with a known result.

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.