This is a frustrating problem that has a simple fix, but it reveals a deeper truth about how structured references in Excel tables work. The user's instinct to lock a range with `$` is correct for standard cell references, but tables use a different syntax: `TableName[ColumnName]`. When you drag a formula horizontally, Excel assumes you want the column reference to shift relative to the new position, just like it would with `A1` becoming `B1`. That's why the `Employee Type` column changes to whatever column sits next to it. The solution is to use an absolute structured reference: `Table[[Employee Type]:[Employee Type]]`. By doubling the brackets and repeating the column name, you tell Excel to lock that specific column regardless of where the formula is dragged. It's not intuitive, but once you know it, you'll never fight the drag again.
What this means for you in practice is that your workflow doesn't have to slow down for a formula quirk. You can build a single `COUNTIFS` statement for week one, drag it across the remaining weeks, and trust that only the date column advances while the employee type column stays fixed. No manual editing, no copying and pasting individual formulas, no wasted time. The fix is a minor syntax adjustment, but the payoff is real: you keep your focus on the data, not on fixing broken references. And if you ever need to lock multiple columns, the same double-bracket pattern applies to each one.
We think this problem is worth calling out because it's a common blind spot for anyone moving from classic ranges to Excel tables. Tables are powerful, they auto-expand, they make formulas readable, and they reduce errors. But they also introduce a different logic for absolute and relative references. The user's confusion is completely understandable, and the solution is not something most training materials cover. That's why we're saying it plainly: `Table[[Employee Type]:[Employee Type]]` is your new best friend. Learn it once, and you'll never wonder why your COUNTIFS breaks when you drag across a row of weekly columns.