Roll — attendance tracker

Attendance
One letter per person per day: P present, A absent, L late, E excused, H holiday. Weekends are shaded. Counts on the right.
Month	September 2026
Days	30
Name		1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27	28	29	30	Present	Late	Absent	Excused	Days	Attendance
Amina Farouk		P	P	P	P			P	L	A	P	P																				7	1	1	0	9	88.9%
Ben Lowe		P	P	A	A	P			P	L	P	P																				6	1	2	0	9	77.8%
Chloé Martin		P	P	P	L			P	P	P	A	P																				7	1	1	0	9	88.9%
Daniel Reyes		P	P	P	P			P	P	P	P	P																				9	0	0	0	9	100.0%
Eva Sørensen		P	L	E	P			P	P	P	P	P																				7	1	0	1	9	88.9%
Farid Haddad		P	P	P	P			P	P	P	P	P																				9	0	0	0	9	100.0%
Grace Okafor		P	P	P	P			P	P	P	P	P																				9	0	0	0	9	100.0%
Hiro Tanaka		P	P	L	P			P	L	P	P	E																				6	2	0	1	9	88.9%
Ines Kowalczyk		P	P	P	A			P	A	P	P	P																				7	0	2	0	9	77.8%
Jonas Weber		P	P	P	P			L	P	A	P	P																				7	1	1	0	9	88.9%
Present per day		10	10	8	8	1	0	9	9	8	9	9	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
Group attendance	90.0%

Attendance % counts Late as present (they came) and leaves Holidays out of the day count. Below 90% turns red.
Days is the number of days with any mark except H — so unlogged days don't drag the percentage down.

Summary
Everyone ranked by attendance, with the reasons. Sort by any column — the rows are plain references.

Name	Attendance	Present	Late	Absent	Excused	Days logged
Amina Farouk	88.9%	7	1	1	0	9
Ben Lowe	77.8%	6	1	2	0	9
Chloé Martin	88.9%	7	1	1	0	9
Daniel Reyes	100.0%	9	0	0	0	9
Eva Sørensen	88.9%	7	1	0	1	9
Farid Haddad	100.0%	9	0	0	0	9
Grace Okafor	100.0%	9	0	0	0	9
Hiro Tanaka	88.9%	6	2	0	1	9
Ines Kowalczyk	77.8%	7	0	2	0	9
Jonas Weber	88.9%	7	1	1	0	9

Group	Value
Group attendance	90.0%
Below 90%	7
Most absences	Ben Lowe
Best attendance	Daniel Reyes

Setup
The month, the people, the codes, and holidays to grey out.
Month (first day)	2026-09-01
Names		Code	Means	Holidays
Amina Farouk		P	Present	2026-09-07
Ben Lowe		A	Absent	2026-10-12
Chloé Martin		L	Late
Daniel Reyes		E	Excused
Eva Sørensen		H	Holiday
Farid Haddad
Grace Okafor
Hiro Tanaka
Ines Kowalczyk
Jonas Weber

Ten name rows. Insert rows inside the block here and on Month/Summary in the same position for more.
Holiday dates grey out the column on Month and are left out of the day count.

How the formulas work
Every formula in this workbook, in plain words. Nothing is hidden and nothing needs a macro. Formulas are shown without the leading = sign.

Where	Formula	What it does
Month · Present / Late / Absent / Excused	COUNTIF(C6:AG6,"P")	One COUNTIF per code across the person's row.
Month · Days	COUNTA(C6:AG6)-COUNTIF(C6:AG6,"H")	Days that have any mark, minus holidays — unlogged days aren't held against anyone.
Month · Attendance	IF(AL6=0,0,(AH6+AI6)/AL6)	Present plus Late over days logged. Late still counts as there. IF avoids #DIV/0! before any marks.
Month · Present per day	COUNTIF(C6:C15,"P")+COUNTIF(C6:C15,"L")	How many were in, per column.
Month · weekend shading	Conditional format: =WEEKDAY(DATE(YEAR($B$3),MONTH($B$3),C$5),2)>5	Rebuilds the date from month + day number; 6 and 7 are Saturday and Sunday.
Month · holiday shading	Conditional format: =COUNTIF(Holidays, DATE(YEAR($B$3),MONTH($B$3),C$5))>0	If the date is in the Setup holiday list, the column is greyed like a weekend.
Month · code colours	Conditional format: =C6="A" → red, ="L" → yellow, ="P" → green	Three rules on the grid; the dropdown (named range Codes) keeps the letters consistent.
Summary · Most absences	INDEX(A5:A14, MATCH(MAX(E5:E14), E5:E14, 0))	MAX finds the highest absence count, MATCH its row, INDEX the name.