Retention formula to count unique customers that made a purchase in both years

Hi,

I've been searching google and this forum for several days, but haven't found a solution to count unique customers that purchased something in both years (ie. a retention formula), so I was wondering if someone here had a solution. So my data is a table of purchase records that very simply looks like this -

A	B	C	D
Customer ID	Year Purchased
56474	2015
114931	2015
39934	2015
63819	2015
114931	2016
39934	2016
47010	2016

So what I need is, if I put the criteria '2015' in cell D1, and '2016' in cell D2, then in D4 a calculation will count how many unique customers bought something in both 2015 & 2016 (which will be '2' ie. only customer IDs 114931 & 39934 both bought something in 2015 & 2016.)

My data has almost 200,000 rows with around 10,000 different customers, so if someone had a UDF, I'd be ecstatic since that will run faster, but an array formula will suffice.

Thanks in advance for your consideration.

MrShorty, I was aware I could do something manual in a minute using a pivot table, then filtering and expanding things, particularly using the Distinct Count row summation if I use 'Add to the Data Model' option. Since this is such a common analysis question "What is the retention rate of our customers year on year" I was wondering if Excel had something automated by now, where it could show the figures year on year without having to filter anything. Let me know if you discover such a functionality using a pivot table.

Kaper, your UDF works like a treat - thank you very much (greetings to Warsaw from Sydney)! I have the data as a Table named "Customer_Purchases", and the 'Customer ID' column and 'Year' column are named respectively in the table. Is it possible for the UDF to reference the table name and these column names, or alternatively how do I rewrite your code if the Customer ID is in column G and the Year is in column Q?

Thanks again.

FlameRetired, thanks for taking the time to reply. I'll store that formula away.

Kaper, that UDF is exactly what I've been looking for. I've modified it below to accommodate criteria that are strings and not just integers.

Please Login or Register  to view this content.

Thanks again for your help.