Guide · College

Attendance percentage formula in Excel (and Google Sheets)

Quick answer

The attendance percentage formula in Excel is =COUNTIF(B2:Z2,"P")/COUNTA(B2:Z2) when each day is marked P or A, formatted as a percentage. The same formula works in Google Sheets. College, Gambit Studio's attendance tracker app, does this per subject on your iPhone, without a sheet.

Updated 2 min readGambit Studio

Use without installing →Free in your browser, no sign-up

Set up the sheet

  1. Column A: student or subject names. Row 1: the dates.
  2. Mark each cell P (present) or A (absent). Leave future dates empty.
  3. In the first free column, type the formula below and drag it down.
  4. Format the column as Percentage (Home > Number > %).

Copy-ready formulas

You wantFormula (row 2, dates in B:Z)
Attendance %=COUNTIF(B2:Z2,"P")/COUNTA(B2:Z2)
Absences so far=COUNTIF(B2:Z2,"A")
Absences left at 75% (total planned classes in AB2)=ROUNDDOWN(AB2*0.25,0)-COUNTIF(B2:Z2,"A")
Flag below 75%=IF(AA2<0.75,"Below 75%","OK")
With 1/0 instead of P/A=SUM(B2:Z2)/COUNT(B2:Z2)

COUNTA counts only filled cells, so future dates do not lower the percentage. Use semicolons instead of commas if your Excel or Sheets uses a comma as the decimal separator.

Highlight low attendance

Select the percentage column, open Conditional formatting, choose "Less than" and type 0.75 with a red fill. In Google Sheets it is Format > Conditional formatting > Less than.

Schools and districts publish their own attendance policy, which can be stricter than state law. This guide summarizes the law and is not legal advice: check your student handbook or ask the attendance office before you rely on a number.

Frequently asked questions

What people ask most about this, answered with the source.

What is the attendance percentage formula in Excel?
=COUNTIF(B2:Z2,"P")/COUNTA(B2:Z2), formatted as a percentage, when days are marked P or A.
How do I calculate attendance percentage in Google Sheets?
The same formula works: =COUNTIF(B2:Z2,"P")/COUNTA(B2:Z2), then Format > Number > Percent.
How do I count absences in Excel?
=COUNTIF(B2:Z2,"A") counts the cells marked A.
CollegeManage academic life
Use without installingGet