This is a problem that deserves a smarter solution than a 750-row copy-and-paste marathon, and we're glad someone is asking the right questions instead of just grinding through it. The core challenge here, counting unique work weeks per client without double-counting when multiple employees exceed 40 hours in the same week, is exactly the kind of analytical friction that spreadsheet users encounter daily, and it's frustrating precisely because it feels like it should be simple. It isn't. But the good news is that the fix doesn't require an incomprehensible formula or a pivot table that collapses under its own logic.
The approach that works here involves a helper column that flags each row as "1" or "0" based on whether that employee-client-week combination meets the 40-hour threshold, then using a unique count formula that evaluates only the first instance of each client-week pair. In Excel, that means combining the `UNIQUE` function with `FILTER` and `COUNT`, or, if you're working in an older version, an array formula using `SUMPRODUCT` and `COUNTIFS` that divides each qualifying row by the number of times that client-week combination appears. The key insight is that you aren't summing hours; you're counting distinct week numbers per client, and the employees are just the mechanism that triggers the flag. Once you structure the data so that each client-week is counted only once, the rest is just a lookup.
We'll be direct: building a formula that references the full table and returns a clean list of client-week counts is not only possible, it's repeatable and auditable. That matters when the data is headed outside your organization. A pivot table with a calculated field won't solve the double-counting problem because it sums rows, not unique combinations. But a `COUNTIFS`-based helper column that marks each row as "1" only for the first qualifying entry per client per week, and then a simple `SUMIFS` on that column per client, will give you exactly what you need. You can expand that method later by adding more criteria to the `COUNTIFS` without rewriting the entire structure.
The manual approach you described, doing this 750 times, is not a failure of effort. It's a sign that the tool you're using can do more heavy lifting than you've been asked to expect from it. The formula you need exists. It's not magic, and it's not a pivot table. It's just a few functions working together, and once you build it once, it will run for every client on your list. That's the kind of return on time that makes a spreadsheet feel like an ally instead of an obstacle.