Count number of years from series of dates - not occurrences within years but # of years with occurrences

[jg10009]

I have an xls with a user id followed by the date of an occurrence as MM-DD-YYYY. I want to count how many years the user had an occurrence happen, not the number of occurrences in the year. For instance, user id 00062644 should return me 3, as in 3 unique years, and user id 00378886 would return me 2. Please help me with a formula for this. My assumption is that it's an array but I'm struggling to write with the year given the date format.

00062644	7/18/2024​
00062644	7/17/2024​
00062644	7/17/2023​
00062644	7/17/2022​
00062644	7/16/2022​
00378886	1/1/2024​
00378886	7/17/2024​
00378886	7/17/2023​
00378886	1/2/2024​

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Eric W]

Some options:

Book2
	A	B	C	D	E	F
1	62644	7/18/2024		User ID	Excel 365	Excel 2016
2	62644	7/17/2024		62644	3	3
3	62644	7/17/2023		378886	2	2
4	62644	7/17/2022
5	62644	7/16/2022
6	378886	1/1/2024
7	378886	7/17/2024
8	378886	7/17/2023
9	378886	1/2/2024
Sheet3

Cell Formulas
Range	Formula
E2:E3	E2	=ROWS(UNIQUE(FILTER(YEAR($B$1:$B$9),$A$1:$A$9=D2)))
F2:F3	F2	=SUM(--(FREQUENCY(IF($A$1:$A$9=D2,YEAR($B$1:$B$9),""),YEAR($B$1:$B$9))>0))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[jg10009]

Some options:

Book2
	A	B	C	D	E	F
1	62644	7/18/2024		User ID	Excel 365	Excel 2016
2	62644	7/17/2024		62644	3	3
3	62644	7/17/2023		378886	2	2
4	62644	7/17/2022
5	62644	7/16/2022
6	378886	1/1/2024
7	378886	7/17/2024
8	378886	7/17/2023
9	378886	1/2/2024
Sheet3

Cell Formulas
Range	Formula
E2:E3	E2	=ROWS(UNIQUE(FILTER(YEAR($B$1:$B$9),$A$1:$A$9=D2)))
F2:F3	F2	=SUM(--(FREQUENCY(IF($A$1:$A$9=D2,YEAR($B$1:$B$9),""),YEAR($B$1:$B$9))>0))
Press CTRL+SHIFT+ENTER to enter array formulas.

Worked great! Thank you for your help!

 

[Eric W]

Happy to help.

It's still a good idea to update your profile for your next question.