Event attendance reconciliation template
By the Analistable team · Updated · 2 min read
Paste registrations on one sheet and check-in scans on another. The Reconciliation sheet counts scans per registered email and labels each person Attended, No-show or Duplicate scan; the Check-ins sheet flags walk-ins; and the Summary gives attendance rate and paid no-show value.
Part of our guide: How to reconcile data in Excel
What the template does
After an event you have two lists: who registered (from the ticketing tool) and who was scanned or ticked in at the door. Reconciling them answers three questions: who did not come, who came without registering, and whether any ticket was scanned twice. The template does this with COUNTIFS on email, following the reconciliation guide and the walkthrough on matching registrations to attendance.
Sheet-by-sheet guide
| Sheet | Columns | You fill in? |
|---|---|---|
| Registrations | Email, Name, Ticket type, Paid | Yes |
| Check-ins | Email, Check-in time, Registered? | Yes (first two columns) |
| Reconciliation | Email, Name, Ticket type, Paid, Scans, Status | No (formulas) |
| Summary | Metric, Value | No (formulas) |
How to fill it in
- Paste the registration export on Registrations, one row per ticket holder.
- Paste the door scans or sign-in sheet on Check-ins. Copy the Registered? formula down to the last scan.
- Copy the Reconciliation formulas down to the number of registrations.
Email is the join key because both systems usually capture it. COUNTIFS ignores upper and lower case, so DEV@example.com matches dev@example.com. Spaces still break matches; see joining on email addresses safely.
How the formulas work
Scans per registrant, and the status label:
=COUNTIFS('Check-ins'!$A$2:$A$500,A2)
=IF(E2=0,"No-show",IF(E2>1,"Duplicate scan","Attended"))Walk-ins are the reverse check, from the Check-ins side:
=IF(COUNTIFS(Registrations!$A$2:$A$500,A2)>0,"Yes","Walk-in")Worked example
Eight registrations and seven scans. Ana was scanned twice, Dev's scan uses capitals, and Ivy turned up without registering:
| Metric | Value |
|---|---|
| Registered | 8 |
| Attended (registered) | 5 |
| No-shows | 3 |
| Duplicate scans | 1 |
| Walk-ins | 1 |
| Attendance rate % | 62.5 |
| Paid value of no-shows | 98 |
Ben, Gus and Hal are the no-shows. Ben and Gus paid 49 each and Hal had a free ticket, so 98 of paid tickets went unused. That figure helps when deciding whether to chase no-shows, send a recording, or overbook free tickets next time.
Limits and when to move on
- Group bookings where one email buys several tickets for different guests need a ticket ID as the join key instead of email.
- Multi-day or multi-session events need one Check-ins sheet per session, or a Session column added to the COUNTIFS.
- Paper sign-in sheets need typing up first; capture email rather than name so the match is reliable.
- If you run a series of events, combine the ticket exports first, or load them all into Analistable and ask which people registered for three events but attended none.
Frequently asked questions
- Can I use names instead of emails?
- Only if names are unique and spelled identically in both lists. Email or ticket number is much safer.
- Does a duplicate scan count as attended?
- Yes. The person is in the Attended count once; the Duplicate scan label just tells you a ticket went through the scanner twice.
- Will it work in Google Sheets?
- Yes. Sheet names with spaces or hyphens are quoted, which Google Sheets accepts.