The QR check-in system is a smart move for your pantry. The data problem you've described is exactly the kind of friction that keeps well-intentioned volunteers tethered to spreadsheets instead of serving neighbors, and it doesn't have to be that way.
You've already done the hardest part: you moved from paper to digital intake. Now you need the data to work as fast as the scan does. A "Form Responses" sheet feeding a "Main Data Hub" is a classic setup, and the transition logic you're describing, new guest becomes returner after a second visit, is a straightforward conditional. A combination of `VLOOKUP` or `XLOOKUP` on the guest's unique identifier (their email or a household ID) against your existing records will tell you whether they exist in the hub. If they don't, the row lands in "New Intake." If they do, it goes to "Returning Guests." The trick is to run that check on form submission, not manually each week. Google Sheets' `ONEDIT` trigger or a simple array formula can automate that distinction so you never copy-paste again.
The weekly tally and monthly rollup is equally solvable with a pivot table or a `COUNTIFS` formula keyed to week numbers and guest status. You can build a dashboard sheet that pulls those counts automatically, and a yearly total is just a sum of the monthly numbers. The "set it and forget it" goal is realistic, but only if you invest an hour now to map out the logic once. Many pantry volunteers get stuck because they treat each week's data as a fresh problem rather than a repeating pattern. Your pattern is clear: scan, classify, tally. A single `QUERY` function can handle all three steps in one formula.
What you're really asking for isn't a formula, it's permission to stop treating your data like a manual ledger. The tools exist, they're free, and they're designed for exactly this kind of workflow. Your team's time is better spent on the floor of the pantry than in the rows of a sheet. Build the automation once, test it with a few mock scans, and then watch the dashboard update itself. That's the whole point of moving to a smarter system: not just faster check-in, but a system that frees you to focus on the people, not the paperwork.