There's a moment in every spreadsheet user's life when the data stops being rows and columns and starts being people. The person asking this question is there right now, staring at a percentage-based allocation and realizing that a simple sum will double-count the very individuals they're trying to track. That moment is where most spreadsheet tools fail, not because the math is hard, but because the logic of "how many unique humans does this represent?" doesn't fit neatly into a cell.
The request here is deceptively simple: count individuals across buildings when each person's time is split as a percentage. The user has already identified the core problem, which is that a person at 50% in Building A and 50% in Building B should not be counted twice, but they should be counted as half a person in each place. That's not a flaw in their data. It's a flaw in how we're trained to think about counts. Most formulas assume whole numbers because most spreadsheet logic assumes you're counting transactions, not people.
What this user needs is a smarter percentage-based formula, one that treats each person as a fraction of a whole rather than as a duplicate entry. The practical move is to use a SUMPRODUCT or SUMIFS approach that sums the percentages but divides by the total allocation per person, or better yet, uses a helper column to calculate each person's contribution to each building as a proportion of their total allocation. That way, if someone is 100% allocated across three buildings, each building gets a fractional share that adds up to one. It's not about avoiding double-counting by hiding rows. It's about redefining what "count" means in the first place.
The real insight here is that the formula isn't the hard part. The hard part is recognizing that your data model needs to reflect the reality that people aren't binary. They're not either in Building A or Building B. They exist across spaces, and your spreadsheet should honor that nuance. The user is asking for a formula, but what they're really doing is asking for permission to stop forcing their human workforce into rigid categories. That's a shift in mindset, and it's the kind of shift that makes a tool feel less like a grid and more like a partner.
So here's the concrete takeaway: stop trying to make one formula do all the work. Build a small table that lists each person, their total allocation, and then use that to calculate building-level headcounts as sums of percentages divided by each person's total. That gives you a true unique-individual count across buildings, no double-counting, no guesswork. It's not flashy, but it's honest. And in a world where spreadsheets are supposed to simplify complexity, honesty is the most underrated feature of all.