Counting duplicate dates is a deceptively hard problem in traditional spreadsheets. The user who posted this question had the right instinct with `COUNTIFS`, but that formula counts every row, not every unique day. The result is a total that repeats entries, which misses the entire point of the ask. They want to know how many distinct days appear in 2025 versus 2026, not how many total rows each year contains. That distinction matters for anyone tracking event attendance, project milestones, or any dataset where a repeated date shouldn't inflate the count.
The standard Excel workaround involves a helper column with `--(FREQUENCY(...))` or a more recent `UNIQUE` function, but both require extra steps and careful range management. The user explicitly ruled out pivot tables, likely because they want a formula-based solution that stays dynamic. This is where the limits of traditional tools become frustrating. You're doing the mental work, identifying the year boundary, isolating dates, eliminating duplicates, but the software forces you to chain multiple functions together in ways that are easy to break. The solution exists, but it's buried in nested syntax that most users will need to search for, not discover on their own.
What this user really needs is a system that understands intent. Instead of writing `COUNTIFS` and then manually deduplicating, they should be able to say "count the unique days per year" and have the tool interpret both the grouping and the uniqueness condition. AI-native spreadsheets are beginning to close that gap by letting you express questions in natural language. The formula becomes unnecessary when you can simply ask: "Show me the unique dates grouped by year, with a count for each." That shift doesn't require users to memorize obscure function combinations. It respects their existing knowledge and removes the friction that makes simple tasks feel like research projects.
The practical takeaway here is straightforward: if you're spending time on workarounds for what should be a basic analytical question, the tool is failing you, not your ability. Counting unique days by year is not an edge case. It's a routine operation that deserves a direct path. The next generation of spreadsheets should let you ask the question and get the answer without becoming a syntax expert. Until then, the `UNIQUE` and `YEAR` functions combined with `COUNTA` will get you there, but the moment you find yourself stacking three formulas for a single year's count, it's worth asking whether you should be exploring a tool that thinks in the same dimensions you do.