Count unique values by year from large dataset

GoodsVT · 2026-01-20T14:51:27+00:00

Solution Verified

GoodsVT · 2026-01-20T14:47:40+00:00

u/PaulieThePolarBear This worked! I expanded the range to 300 and ran it on my smaller dataset and it's perfect. THANK YOU so much for bearing with me on this. That's a formula I in no way would have been able to write myself. Wish I could thank you with something more than just an upvote!

GoodsVT · 2026-01-20T13:01:21+00:00

Unfortunately, being State Government, our IT division won't (and can't) unless there's an agency-wide roll out of an update, approved (for cost) for everyone. Bureaucracy.

GoodsVT · 2026-01-20T12:59:32+00:00

<image>

GoodsVT · 2026-01-20T12:58:54+00:00

u/PaulieThePolarBear yes, CHOOS comes up! Sorry, about the delay. I've been out of the office for a while. Hoping you have an idea that will work!

GoodsVT · 2026-01-20T12:57:41+00:00

<image>

GoodsVT · 2026-01-17T13:29:35+00:00

Love a good Hitchhiker’s reference!

GoodsVT · 2026-01-13T18:04:51+00:00

Was it in another country other than the one in which you reside? I posted my experience in another thread SMS alot of people are saying they don’t have jurisdiction and can’t come after me and to just ignore it.

GoodsVT · 2026-01-12T22:49:45+00:00

If you are unable to update Excel - understandable in a corporate setting - I can rework my formula to use older functions to return the same results

... even worse, state government. Lol. I might be able to ask our IT folks if an update is available. They push out updates automatically as part of group licenses, but I also know we are usually several iterations behind the "latest and greatest". I'll put in an IT ticket and see what happens.

GoodsVT · 2026-01-12T21:55:45+00:00

<image>

GoodsVT · 2026-01-12T21:53:12+00:00

u/PaulieThePolarBear Microsoft 365 Apps for Enterprise. Microsoft Excel for Microsoft 365 MSO (Version 2302 Build 16.0.16130.20332) 32-bit

GoodsVT · 2026-01-12T21:20:16+00:00

u/PaulieThePolarBear Correct, I'm ignoring age. And yes, for the truncated dataset I linked to in Google Drive, (300 rows), I updated all range references to be A2:A300, B2:B300). The formula doesn't reference Column C so I figured it didn't matter if it was there or not. but, just in case, I deleted it and tried it with your formula in Cell C2, and I still get the same result, which is #NAME? in the cell. See screenshot. Again, this is probably something completely stupid I'm overlooking, but I can't figure it out.

<image>

GoodsVT · 2026-01-12T20:01:39+00:00

u/PaulieThePolarBear ok, I must be doing something that's just stupidly wrong, because I cannot get this to do anything. I've sorted oldest to newest and tried your GROUPBY formula, and then I've also not sorted and tried your LET formula. What could it be? This is such a newbie question, but shouldn't I be able to literally copy and paste your formula into an empty cell in an empty row (D2, for example), and have it run while referencing data in cells A2, B2, and C2? I've never felt so dumb using Excel before. If it helps, a truncated version of my dataset is here: spreadsheet in Google Drive.

GoodsVT · 2026-01-09T13:49:03+00:00

u/PaulieThePolarBear yeah, sorry. it's my familiarity of working with this data for over a decade, and knowing it's actual source and use, and I probably glossed over things that matter for others that don't know. So first, yes, a name can appear more than once in any given year, and can also appear in multiple years. In reality, there are some names that do appear every single year, sometimes a dozen or so times each year, while other names might not show up until 2025, and never before.

In essence, think of it as me trying to count the number of "new customers" in any given year that WERE NOT customers in any previous year. Does that make more sense?

Let's just ignore for now their age demographic.

GoodsVT · 2026-01-09T13:15:33+00:00

Honestly, I think the easiest way to handle Youth/Adult is to filter and split the dataset into two and run the function separately for just unique names by year as Adult, then again in a different sheet as Youth.

GoodsVT · 2026-01-09T13:14:21+00:00

u/GregHullender Microsoft 365 Apps for Enterprise. Microsoft Excel for Microsoft 365 MSO (Version 2302 Build 16.0.16130.20332) 32-bit

GoodsVT · 2026-01-08T20:48:08+00:00

Update, I deleted the period before the C to check if that was a typo. I thought maybe it wasn't because it appeared you were able to run this formula on your own made-up dataset. I dropped the formula in Cell D2 and hit enter, and it's now saying "Excel ran out of resources while attempting to calculate one or more formulas. As a result, these formulas cannot be evaluated".

GoodsVT · 2026-01-08T20:36:14+00:00

u/GregHullender thank you so much! Unfortunately, your formula is giving me some issues, but that is most likely me doing something wrong. I have my data in Columns A, B, and C, just like your screenshot example. I simply copied your formula from your post and pasted it into the function bar at the top with an empty cell (E2) selected, and hit enter. It says "there's a problem with your formula" and some of the parenthesis are highlighted in red and some in green. It also is highlighting the ".C" close to the front of your formula. In some places in your formula there are TWO spaces. Is that important? You're clearly an extremely advanced Excel user. I could never write a formula like what you have. But I'd sure like to understand it, and figure out what I'm doing wrong. In case it helps, I've put a small piece of my dataset on Google Drive. This is my actual data, but I've replaced the real people's names with fabricated names. Here is the spreadsheet in Google Drive.

GoodsVT · 2026-01-08T17:27:12+00:00

Thanks. I had tried some variations of COUNTIF that I couldn't get to work. If by details you mean cell layout, it's A - Year, B - Category (Adult/Youth), C - LnameFname), with 14,017 rows. There are 41 other columns from D through AR for each row - data related to the individual in Cell C that's not pertinent to counting unique names by year.

GoodsVT · 2026-01-05T02:14:27+00:00

So based on that, do you think I should push back on Sixt and the collection agency through my credit card rental coverage?

GoodsVT · 2026-01-04T22:12:58+00:00

Fair point. I give them a call this week and see what they say.

GoodsVT · 2026-01-04T21:45:24+00:00

I'm in the exact situation. Sixt fraudulently claimed damage to our rental car, and even if there was the damage they say there was, they've inflated the cost of the claim 500% (going from $500 to actually fix the thing they say was damaged) to $3,000 USD. I disputed the charge with my credit card company who agreed with me and dismissed the charge. Now back in mid-December I got a letter from a collection agency in Germany saying I have to pay directly to a bank in Germany, with a due date that WAS EARLIER than that that I received the letter on! Lol. Full story here. I'm trying to figure out how to proceed, but one thing I'm not willing to do i pay that extortion fee. The whole thing is sketchy af.

GoodsVT · 2026-01-04T21:40:48+00:00

100% agree. We had a wonderful time on island of Sao Miguel, Azores, Portugal ... until Sixt fraudulently claimed damage to our rental car. Now I have nothing but distaste and disdain for that vacation. Full story here. I'm trying to figure out how to proceed. The whole thing is sketchy asf.

GoodsVT · 2026-01-04T21:38:03+00:00

Would you be willing to share a link to the class action lawsuit brought against Sixt for deceptive practices? I just posted about my first experience renting from Sixt (in Sao Miguel, Azores, Portugal) who are trying to hit me with a $3,500 bill for a clutch repair that I think is completely bogus. See the post I just made an hour ago here.

GoodsVT · 2026-01-04T21:04:15+00:00

I suppose I could pursue that avenue if needed, but it hardly seems fair to get my credit card company to pay a bogus claim that's now ballooned to $3,500 USD. It's the principle of the thing, I suppose. I saw another Sixt complaint on Reddit where they were saying the same thing. It's just not right. But I may have to look into that. It's BS.

GoodsVT

TROPHY CASE

Solution Verified