Build an RSVP Meal Summary with Exception Checks in Google Sheets

A meal count is not ready just because a formula returns a number. Build a small RSVP sheet that distinguishes a confirmed attendee with a missing choice from someone who declined, and excludes duplicate IDs until they are resolved.

Scope: Google Sheets in a desktop browser with an existing personal Google account and an editable blank spreadsheet. No paid Workspace feature, Google Form, Apps Script or add-on is needed. No account access, sharing or application execution was performed during preparation. The web service has no fixed desktop release number; documentation was reviewed on 21 October 2026. Formulas use English names and commas; adapt separators to your spreadsheet locale.

Enter one person per row

Enter this fictional table in A1:C7. Leave C6 truly empty where shown; do not type the words “leave blank”.

Attendee ID	RSVP	Meal
P01	Yes	Vegetarian
P02	Yes	Standard
P03	No	None
P04	Pending	None
P05	Yes	(leave blank)
P06	Yes	Vegetarian

One row means one person, not one household. Use nonblank IDs made of letters and digits with consistent capitalization and no spaces or wildcard characters. IDs do not reveal real names. Do not put allergy or other sensitive information into this public practice dataset; this simplified menu is not an allergy-management system.

For repeat entry, use the existing dropdown lesson to offer Yes/No/Pending in B2:B7 and Vegetarian/Standard/None in C2:C7. Dropdowns reduce typos but do not resolve missing choices or duplicate people.

Build readable checks in separate columns

Enter ID check, Response check, Ready in D1:F1. Put these formulas in row 2 and copy D2:F2 through row 7:

• D2: =IF(A2="","CHECK ID",IF(COUNTIF($A$2:$A$7,A2)=1,"OK","CHECK ID"))
• E2: =IF(B2="Yes",IF(OR(C2="Vegetarian",C2="Standard"),"OK","CHECK MEAL"),IF(OR(B2="No",B2="Pending"),IF(C2="None","OK","CHECK MEAL"),"CHECK RSVP"))
• F2: =IF(D2<>"OK",D2,IF(E2<>"OK",E2,"READY"))

Separating ID and response checks makes it possible to inspect both even when the final status shows only the ID problem. Keep the dollar signs around A2:A7 so every row searches the complete ID list; see fixed references.

Expected D2:D7: all OK. Expected E2:E7 and F2:F7: only row 6 needs attention, with CHECK MEAL; other E cells show OK and other F cells READY. A blank or unrecognized RSVP produces CHECK RSVP, not an automatic decline. No and Pending require the explicit meal label None.

COUNTIF matching is not case-sensitive and supports wildcards. This bounded ID convention deliberately avoids those ambiguities. It cannot establish that two different IDs do not belong to the same person; compare against the authoritative invitation list before a real handoff.

Count only ready confirmed meals

Put the following labels in H1:H6 and formulas in I1:I6:

Label	Formula	Initial result
Yes response rows	=COUNTIF(B2:B7,"Yes")	4
Ready confirmed people	=COUNTIFS(B2:B7,"Yes",F2:F7,"READY")	3
Vegetarian meals	=COUNTIFS(B2:B7,"Yes",C2:C7,"Vegetarian",F2:F7,"READY")	2
Standard meals	=COUNTIFS(B2:B7,"Yes",C2:C7,"Standard",F2:F7,"READY")	1
Rows needing correction	=COUNTIF(F2:F7,"<>READY")	1
Pending response rows	=COUNTIF(B2:B7,"Pending")	1

There are four Yes rows but only three usable meal choices. The summary is provisional while I5 is nonzero. Check that I3+I4 equals I2; that equality alone does not mean every Yes response is ready. Pending responses remain visible and are not ordered as meals under this exercise’s rule.

COUNTIFS ranges must have matching dimensions. Do not extend the RSVP range while leaving the meal or readiness range at row 7.

Resolve missing data and challenge duplicate handling

For this fictional case, P05 confirms Standard. Enter Standard in C6. I1:I6 must become 4, 4, 2, 2, 0, 1. Independently enumerate the confirmed attendees: P01 and P06 Vegetarian; P02 and P05 Standard.

Now temporarily change A7 from P06 to P02. D3 and D7 must both show CHECK ID, and F3/F7 must also show CHECK ID. The summary becomes 4, 2, 1, 1, 2, 1. Four Yes rows still exist, but neither occurrence of the repeated ID is trusted. Do not silently keep the first response or count the two rows as two people. Restore A7 to P06 and verify the corrected summary returns.

As a missing-ID test, temporarily clear A2. D2 and F2 must show CHECK ID. Restore P01 before proceeding. These tests are reader acceptance checks, not reported application runs.

Prepare a controlled handoff

Name the spreadsheet clearly and record the event and summary date next to the model. Before using the figures, require zero correction rows, check the invitation list for missing people and decide how late/Pending responses will be handled. A clean row-level report does not prove everyone has replied.

Keep the identifying input table private. If a real caterer only needs totals, provide approved aggregate counts rather than sharing the entire attendee file. This tutorial does not send or publish anything. For more people, extend every fixed range and copy all three helper formulas to every new input row. Inspect the resulting formulas, not just the total.

Sources and verification scope

Google documents Sheets access with a Google account, COUNTIF and its matching rules, COUNTIFS and equal-size ranges, IF and OR. The fictional roster and acceptance tests are original; counts were independently modeled. No native spreadsheet, save/reopen or screenshot evidence is claimed.