There's a quiet frustration buried in this question, and it's one we hear far too often. The user isn't asking for a complex macro or a custom script. They're asking for a simple, repeatable way to count names across yearly sheets without manually listing a thousand references. That's not a niche request. That's the daily reality for anyone who manages data that grows faster than their patience.
The core problem here is scale, and it's one that traditional spreadsheet design never solved gracefully. When you have a thousand names and more arriving regularly, the usual tricks break down. You can't handcraft a COUNTIF for each one. You can't maintain a master list that stays accurate without constant upkeep. The user is effectively describing a database query, but they're stuck in a tool that expects you to know every value in advance. That mismatch is the real story.
What makes this request notable is that it points to a better way of thinking about spreadsheets altogether. Instead of asking "how do I write a formula for each name," the user is asking "how do I let the system figure out the names for me." That shift is small in words but massive in practice. It means moving from a manual, error-prone workflow to one where the tool does the heavy lifting. For anyone managing annual sheets, client lists, or inventory names, this is the difference between spending an afternoon on busywork and finishing in minutes.
The practical takeaway isn't about a single formula trick. It's about recognizing when your spreadsheet is asking you to do something the tool should handle. If you're manually listing values to count them, you're fighting the system. The solution lies in dynamic arrays, pivot tables, or even a simple query that pulls unique names from column D and counts them across all sheets automatically. The user doesn't need a workaround. They need a workflow that scales with their data, not against it.
So here's our stance: if you're maintaining a list of names just to count them, you're doing it wrong. Let the spreadsheet do the discovering. Start by exploring dynamic array functions or a pivot table that references all sheets at once. The moment you stop handcrafting references and let the tool recognize the names for you, the problem dissolves. That's not a feature request. That's a baseline expectation for anyone who takes their data seriously.