# How to calculate attendance percentage in Excel

Divide the days present by the days recorded: `=COUNTIF(B2:G2,"P")/COUNTA(B2:G2)`, filled down.

**Excel:** `=COUNTIF(B2:G2,"P")/COUNTA(B2:G2)` (Every current version)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D | E | F | G | H |
| --- | --- | --- | --- | --- | --- | --- | --- |
| Student | 2026-03-02 | 2026-03-03 | 2026-03-04 | 2026-03-05 | 2026-03-06 | 2026-03-09 | Attendance |
| Ava | P | P | P | P | P | P |  |
| Ben | P | A | P | L | P | P |  |
| Chloe | P | P | A | P | P |  |  |
| Dan | A | A | P | P | P | P |  |
| Eve | P | P | P | P | L | P |  |
| Finn |  |  | P | P | A | P |  |

Formula in H2, filled to H7: `=COUNTIF(B2:G2,"P")/COUNTA(B2:G2)`

Result: 100.0%; 66.7%; 80.0%; 66.7%; 83.3%; 75.0%

## How it works

COUNTIF counts the P marks in the student's row and COUNTA counts the days that have any mark. Finn joined on March 4, so his two blank days are left out of his total: 3 of 4 recorded days is 75%.

Late (L) counts as not present here.

## Other ways

- `=COUNTIF(B2:G2,"P")/COLUMNS(B2:G2)`: Divides by every school day, so blank days count as absences. (Lowers Chloe to 66.7% and Finn to 50%.)
- `=(COUNTIF(B2:G2,"P")+COUNTIF(B2:G2,"L"))/COUNTA(B2:G2)`: Counts late arrivals as present. (Raises Ben and Eve.)

## Common mistakes

- Pick the base on purpose: days recorded (COUNTA) or all school days (COLUMNS). Schools usually define it in their policy.
- Codes typed in different ways ("p", "Present") break the count. Use a drop-down list for the marks.

## Questions

**How do I flag students below 90% attendance?**

Use `=H2<90%`, or conditional formatting with the same rule.

**How do I count half days?**

Give them their own code, say "H", and add half of them: `=(COUNTIF(B2:G2,"P")+COUNTIF(B2:G2,"H")/2)/COUNTA(B2:G2)`.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/attendance-percentage