# How to count attendance in Excel

Count each student's "P" marks across the day columns: `=COUNTIF(B2:F2,"P")`, filled down.

**Excel:** `=COUNTIF(B2:F2,"P")` (Every current version)

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

## Example

Sample data (row 1 is the header):

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

Formula in G2, filled to G7: `=COUNTIF(B2:F2,"P")`

Result: 5; 3; 4; 3; 4; 3

## How it works

Each row is one student and each column one school day. COUNTIF looks along the row and counts the cells marked "P". Because the range has no $ signs, it moves down a row with each student.

Late (L) and absent (A) are not counted as present here; Ben was present 3 of 5 days.

## Other ways

- `=SUMPRODUCT(--(B2:F2="P"))`: The same count with a comparison. Use it with EXACT if lowercase "p" must not count.
- `=COUNTIF(B2:F2,"P")+COUNTIF(B2:F2,"L")`: Counts late arrivals as present. (Ben and Eve get one more day each for their late marks.)

## Common mistakes

- COUNTIF ignores case, so "p" and "P" both count. Use a drop-down list to keep the codes consistent.
- Blank cells (days not recorded yet) count as neither present nor absent. Count the days recorded with `=COUNTA(B2:F2)`.

## Questions

**How do I calculate the attendance percentage?**

Divide by the days recorded: `=COUNTIF(B2:F2,"P")/COUNTA(B2:F2)`, formatted as a percentage.

**How do I count how many students were present each day?**

Count down the column instead: `=COUNTIF(B2:B7,"P")`, filled across.

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/count-attendance