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

J

jg10009

New Member
Joined
Feb 26, 2010
Messages
23
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​
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
Sort by date Sort by votes
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’)
 
Upvote 0
Some options:

Book2
ABCDEF
1626447/18/2024User IDExcel 365Excel 2016
2626447/17/20246264433
3626447/17/202337888622
4626447/17/2022
5626447/16/2022
63788861/1/2024
73788867/17/2024
83788867/17/2023
93788861/2/2024
Sheet3
Cell Formulas
RangeFormula
E2:E3E2=ROWS(UNIQUE(FILTER(YEAR($B$1:$B$9),$A$1:$A$9=D2)))
F2:F3F2=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.
 
Upvote 0
Solution
Eric W said:
Some options:

Book2
ABCDEF
1626447/18/2024User IDExcel 365Excel 2016
2626447/17/20246264433
3626447/17/202337888622
4626447/17/2022
5626447/16/2022
63788861/1/2024
73788867/17/2024
83788867/17/2023
93788861/2/2024
Sheet3
Cell Formulas
RangeFormula
E2:E3E2=ROWS(UNIQUE(FILTER(YEAR($B$1:$B$9),$A$1:$A$9=D2)))
F2:F3F2=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.
Click to expand...
Worked great! Thank you for your help!
 
Upvote 0
Happy to help. :)

It's still a good idea to update your profile for your next question.
 
Upvote 0
You must log in or register to reply here.

Similar threads

I
  • Solved
Excel Filtering for a Summary Tab
2 3 4
Replies
30
Views
425
ItalianPlatinum
I
D
  • Question Question
Locate first instance of all dates in a column and hide all remaining rows
Replies
4
Views
89
Peter_SSs
Peter_SSs
A
  • Solved
take amount from last row in listbox on form and put into next column
Replies
7
Views
144
Abdo
A
B
  • Question Question
Logging First occurrence in calendar year
Replies
9
Views
368
KRice
K
K
  • Solved
Countifs to find year from Date
Replies
3
Views
131
Eric W
Eric W

Forum statistics

Threads
1,223,931
Messages
6,175,465
Members
452,646
Latest member
tudou

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top