A sign-in list records who came. Nobody in it is absent, because absent people never sign in. Whether the rows come from a Google Form, a paper register typed up later or a QR scanner, the list of who is missing has to be worked out: the people you expected, minus the people who signed in. That means a roster, and one formula that compares the two.
Everything in this guide is ordinary Google Sheets and costs nothing. It covers both kinds of sign-in data people actually have: Google Form responses, whose timestamps are real dates, and rows written by a scanner such as QR to Sheets, whose timestamps are text. Most formulas that fail on attendance sheets fail on exactly that difference.
Written by QR to Sheets, which sells a scanner that writes sign-in rows, described near the end, so weigh that accordingly. The formulas work on any sign-in data, from any source, and every one was checked on 2026-10-08 in a spreadsheet engine against sample data holding both kinds of timestamp. QR to Sheets does not hold your roster or mark anyone absent: the roster and the formulas stay in your own sheet.
What do I need before the formulas work?
Three tabs. The names are up to you, but the formulas below use these:
- Roster: everyone expected. ID in column A, name in column B, class, team or group in column C, starting in row 2 under a header row. This is the only place names are typed.
- The sign-in data. For a Google Form it is the response tab, usually called Form Responses 1, with Google's Timestamp in column A and your questions after it in the order you asked them; the examples assume the ID question is column C and a name question is column D. For QR to Sheets it is the tab your scanner link writes to, here called Log: scanned_at in column A, the scanned value (qr_value) in column B, then scanner_email in column C, which for a scanner link holds link: followed by the link's name, such as link:Sign in, then the workspace name, the device type and a scan ID.
- Absent: formulas only, with the date you are checking in B1. Put =TODAY() there for a live list.
If you use column mapping on a QR to Sheets link, the layout changes: a mapped row starts with the scan time in column A, the link name in column B and a device ID in column C, and your own columns begin at column D. Point the formulas at whichever column holds the ID.
Which kind of timestamp do you have?
Type =ISTEXT(A2) next to your data, pointing at the first timestamp. TRUE means the timestamp is text. QR to Sheets writes each time as YYYY-MM-DD HH:MM:SS in your workspace timezone, and Google Sheets stores it as text. FALSE means a real date and time, which is what a Google Form response sheet holds.
- A date range such as ">="&B1 matches nothing on text timestamps, so every person looks absent.
- A text pattern such as "2026-10-08*" matches nothing on real dates, so the same thing happens the other way round.
- VALUE() turns a text timestamp into a real date and leaves a real date as it is, which is how the formula that works on either kind does it.
Make the IDs match
A scanned or submitted 1042 is text; a 1042 typed on the Roster tab is a number; and text never matches a number, so everyone shows as absent. Give IDs a letter prefix such as S1042, or format the Roster's column A as Format, Number, Plain text before typing. The guide to showing the name for a scanned ID covers this trap and the lookups that put names next to IDs.
Who is absent today, from scan rows with text timestamps?
In A2 of the Absent tab, with the date in B1:
- Absent: =FILTER(Roster!A2:B, Roster!A2:A<>"", COUNTIFS(Log!B:B, Roster!A2:A, Log!A:A, TEXT($B$1,"yyyy-mm-dd")&"*")=0)
- COUNTIFS counts, for each person on the roster, the rows on the Log with their ID and a timestamp that starts with the chosen date. TEXT($B$1,"yyyy-mm-dd")&"*" turns the date into a pattern such as 2026-10-08*, and Google's COUNTIF help confirms that * matches zero or more characters. FILTER keeps the people whose count is zero.
- Before the first sign-in of the day the formula lists everyone, which is correct. As rows arrive, names drop off the list.
- A person scanned twice counts once. The formula asks whether any row exists, not how many.
- Any row counts as present, on any link. If you run separate Sign in and Sign out links, someone with only a Sign out scan counts as present. To count arrivals only, add the link as a condition: =FILTER(Roster!A2:B, Roster!A2:A<>"", COUNTIFS(Log!B:B, Roster!A2:A, Log!C:C, "link:Sign in", Log!A:A, TEXT($B$1,"yyyy-mm-dd")&"*")=0)
- The link condition is also how you check one session, one room or one entrance: name a scanner link for each, and match its exact name with link: in front.
Google's FILTER help notes that when nothing satisfies the conditions, FILTER returns #N/A. Here that means nobody is absent. Wrap the formula as =IFERROR(FILTER(...), "Nobody absent") to show a message instead.
Who is absent from a Google Form?
A Google Form response sheet holds real dates, so the date test is a range from midnight to midnight instead of a text pattern. With the ID question in column C of Form Responses 1:
- Absent: =FILTER(Roster!A2:B, Roster!A2:A<>"", COUNTIFS('Form Responses 1'!C:C, Roster!A2:A, 'Form Responses 1'!A:A, ">="&$B$1, 'Form Responses 1'!A:A, "<"&($B$1+1))=0)
- The sheet name has spaces, so it goes in single quotes. If you renamed the tab, use your name.
- If the form collects email addresses, put each person's email in column C of the Roster and match the form's email column against it instead of an ID.
When people typed their names
Typed names are where form attendance gets messy. LOWER() puts both sides in lower case, and TRIM, in the words of Google's help, removes leading, trailing, and repeated spaces in text, so this version still matches ana lopez typed in lower case and Chloe Park typed with a double space. With the name question in column D:
- Absent by name: =FILTER(Roster!B2:B, Roster!B2:B<>"", ISNA(MATCH(LOWER(TRIM(Roster!B2:B)), ARRAYFORMULA(LOWER(TRIM(FILTER('Form Responses 1'!D2:D, INT('Form Responses 1'!A2:A)=$B$1)))), 0)))
- It does not catch Anna for Ana, a missing accent, or a nickname. Each of those shows as absent, which is the honest result and the reason to collect an ID rather than a name.
One formula for either kind of timestamp
If one tab mixes both kinds, or you would rather not check, convert each timestamp with VALUE() and compare whole days. This version works on scan rows and on form responses alike; point it at your own columns:
- Absent, any timestamp: =FILTER(Roster!A2:B, Roster!A2:A<>"", ISNA(MATCH(Roster!A2:A, FILTER(Log!B2:B, IFERROR(INT(VALUE(Log!A2:A)), 0)=$B$1), 0)))
- INT() drops the time of day so the comparison is by date. IFERROR(..., 0) skips blank or unreadable rows.
- Before the first sign-in of the day the inner FILTER finds nothing and the formula lists everyone, which is right.
How many are absent, and who is present?
- Number absent: =SUMPRODUCT((Roster!A2:A<>"")*(COUNTIFS(Log!B:B, Roster!A2:A, Log!A:A, TEXT($B$1,"yyyy-mm-dd")&"*")=0))
- Number present, each person once however many times they scanned: the same formula with >0 in place of =0.
- A Present or Absent column on the Roster itself, in D2 and filled down: =IF(COUNTIFS(Log!B:B, A2, Log!A:A, TEXT(Absent!$B$1,"yyyy-mm-dd")&"*")>0, "Present", "Absent")
- Codes scanned that day that are not on the roster, usually a new person or a test scan: =UNIQUE(FILTER(Log!B2:B, LEFT(Log!A2:A,10)=TEXT($B$1,"yyyy-mm-dd"), COUNTIF(Roster!A2:A, Log!B2:B)=0))
- For a Google Form, swap each text pattern for the date range shown above.
How do I see absences across several dates?
A pivot table of the sign-in log only lists people who signed in at least once, so someone who never came is missing from it entirely. A grid built from the roster lists everyone. On a Grid tab, put the IDs in column A from A2, with =FILTER(Roster!A2:A, Roster!A2:A<>""), and real dates across row 1 from B1. Then in B2:
- Present or absent: =IF(COUNTIFS(Log!$B:$B, $A2, Log!$A:$A, TEXT(B$1,"yyyy-mm-dd")&"*")>0, "P", "A")
- Fill it right across the dates and down the people. The dollar signs keep the ID column and the date row fixed while everything else moves.
- Absences per person, at the end of each row: =COUNTIF(B2:Z2, "A"), with Z replaced by your last date column.
- Add a date to row 1 only for days the group actually met, or every holiday counts as an absence for everyone.
- For a Google Form, use COUNTIFS('Form Responses 1'!$C:$C, $A2, 'Form Responses 1'!$A:$A, ">="&B$1, 'Form Responses 1'!$A:$A, "<"&(B$1+1)) in the same place.
If the marks have to go into a student information system, build them on a separate tab and export that tab, never the raw log. Exporting QR attendance to an SIS covers the shapes those systems expect.
When did an absent person last sign in?
- Last row for one ID, searching up from the bottom: =XLOOKUP(A2, Log!B:B, Log!A:A, "Never", 0, -1)
- The last argument, -1, makes XLOOKUP search from the last row up, so it returns the most recently written row for that ID, or Never.
- Most recently written is not always most recent. A phone that lost signal sends its saved scans when the signal returns, so those rows can land below later ones. For a follow-up call that rarely matters; for anything exact, take the latest time with MAX over VALUE() of the matching timestamps.
Why does my absent list show everyone, or nobody?
- Everyone absent: the wrong date test for your timestamps (run the ISTEXT check), IDs stored as numbers on one tab and text on the other, or a date in B1 with no rows yet.
- A list that is wrong for an hour or two around midnight: TODAY() changes at midnight in the spreadsheet's time zone. Set it under File, Settings to the same zone as your sign-ins. For QR to Sheets rows, that is the timezone set for your workspace; timestamps in the wrong time zone explains both settings.
- Someone absent who was there: a typo in a typed ID or name, a trailing space, or a sign-in on a different tab or form. Check their rows on the log.
- Someone present who left early: the formula answers whether a person signed in at all that day. Sign-out times need a second link and a second test, which the after-school sign-in and sign-out guide sets up as a daily roll call.
- #REF! instead of a list: something is typed in the cells the list needs to spill into. Clear the cells below and to the right of the formula.
Are there add-ons or tools that do this for you?
Yes, for particular cases. Alice Keeler's article on finding who did not fill out a Google Form, read on 2026-10-08, recommends her Missing From add-on, which, in her words, helps you to match up a list of email addresses you were expecting to fill out the Form with the email addresses that were submitted. It needs the form to be collecting email addresses and an expected-participants sheet with a header containing the word email, and it adds a new sheet listing who did not fill out the form. It suits a one-off survey or a form where everyone signs in with Google. It does not look at dates, so a daily register needs the formulas above.
For events with a guest list, the QR to Sheets Events add-on imports the list from your Google Sheet and, when staff scan a guest's code at the door, writes the check-in time back to that guest's row, so anyone with a blank check-in cell has not arrived. It requires the paid plan, and costs 25 USD a month on top. For a recurring program where children sign in and out every day, the after-school sign-in and sign-out guide puts these formulas into a daily roll call.
| Option | What it compares | Handles dates | Needs | Cost |
|---|---|---|---|---|
| Google Sheets formulas in this guide | A roster tab against any sign-in rows | Yes, any date or range | A roster with IDs or names | Free |
| Missing From by Alice Keeler | Expected email addresses against form submissions | No date filter described | A form collecting email addresses and an email column | Not stated in the article read |
| Google Form on its own | Nothing; it lists who submitted | Its timestamps are real dates | A roster and a formula to find who is missing | Free |
| QR to Sheets scanner link plus these formulas | A roster tab against scan rows | Yes, timestamps are text in the workspace timezone | Printed codes and a staff phone | Free for one phone, one link and 300 scans in total; then 7 USD per phone per month |
| QR to Sheets Events add-on | A guest list against door check-ins written back to each row | Per event | A guest list in Google Sheets and the paid plan | 25 USD a month on top of the paid plan |
Where QR to Sheets fits
QR to Sheets is the sign-in half. You connect your Google Sheet and create a scanner link pointed at the Log tab. A teacher, coach or volunteer opens the link in a phone browser and scans each person's printed card, and each scan lands as a timestamped row. Nothing is typed, so the IDs match the roster and the typed-name problems above disappear. The free QR code ID card maker prints the cards from a pasted list, with no signup.
- Each phone that scans counts as one device. Free covers one phone; Premium is $7 per phone per month. People being scanned never open the link and need nothing.
- If the signal drops, the open scanner keeps saving scans on the phone and sends them when the signal is back, each with its original scan time.
- A repeat scan of the same card on the same link within 12 hours is saved anyway and flagged with an amber notice; the formulas above count each person once.
- It does not keep a roster, mark absences or show names on the phone. Those stay in your sheet, built with the formulas above.
- A scan proves a card was presented, not who presented it, so a staff member holding the phone matters more than the code.
| Capability | Google Form + roster formulas | QR to Sheets |
|---|---|---|
| Cost | Free | Free for one phone, one link and 300 scans in total; then 7 USD per registered device per month |
| How a person signs in | Types an ID or name on their own phone | A staff member scans their printed card |
| Typos in IDs and names | Common, and each one looks like an absence | Rare, because the code is read rather than typed |
| Timestamp | A real date in the spreadsheet's time zone | Text in the workspace timezone, matched as text or read with VALUE() |
| Who is absent | The formulas in this guide | The formulas in this guide |
| Signal drops | The form cannot submit | Scans are kept on the phone and sent when the signal is back |
| Where the data lives | Your Google Sheet | Your Google Sheet |
Step by step
- 1
Make a Roster tab of everyone expected
One row per person: ID in column A, name in column B, group in column C. Format column A as plain text before typing, or give IDs a letter prefix, so they match what was submitted or scanned.
- 2
Check which kind of timestamp you have
Put =ISTEXT(A2) beside your sign-in data. TRUE means text, which is what QR to Sheets writes. FALSE means a real date, which is what Google Forms writes. The two need different date tests.
- 3
Put the date in one cell
On an Absent tab, put =TODAY() in B1 for a live list, or type a date to check another day. Every formula below reads the date from that cell.
- 4
Add the absent formula for your source
Use the scan-row version for text timestamps, or the Google Form version for real dates. Both list the ID and name of everyone on the roster with no sign-in on that date.
- 5
Add a count and a grid if you report on it
A SUMPRODUCT counts absences for the day, and a grid with dates across the top marks each person present or absent for every date, ready to total per person.
Frequently asked questions
How do I find who did not fill out my Google Form?+
List everyone expected on a Roster tab, then filter it for people with no response: =FILTER(Roster!A2:B, Roster!A2:A<>"", COUNTIFS('Form Responses 1'!C:C, Roster!A2:A)=0) for all time, or add a date range on the Timestamp column for one day. The Missing From add-on by Alice Keeler does a version of this by email address.
Why does my absent formula list everyone?+
Usually the date test does not fit the timestamps. Google Form timestamps are real dates and need a range such as ">="&B1. QR to Sheets timestamps are text and need a pattern such as TEXT(B1,"yyyy-mm-dd")&"*". Check with =ISTEXT(A2), and check that IDs are text on both tabs.
Can I list who was absent on a past date?+
Yes. Put the date in the cell the formula reads, instead of =TODAY(). For several dates at once, build a grid with dates across the top and mark each person P or A with COUNTIFS.
Does QR to Sheets mark people absent automatically?+
No. It writes a timestamped row for each scan to your Google Sheet. The roster and the absent list are yours, built with the formulas in this guide, so the list of people never leaves your spreadsheet.
How do I count each person's absences for the month?+
Build a grid with the roster down column A and each meeting date across row 1, mark each cell P or A with COUNTIFS, then count the A cells in each row with COUNTIF. Only add dates on which the group actually met.
Does someone who scanned twice count twice?+
No. Every formula here asks whether a person has at least one row on the date, so two scans, or a form submitted twice, still count as one person present.