Summing the number of instances of an item in each year

[JohnD76]

Hi all, first time asking a question here so please bear with me.

I have a large table of ID Nos. that are listed by the multiple dates they mentioned in our system.

As well as sorting the lists by these IDs and by the date, I've also been asked to sum how many times each ID occurs within each year. I assume I can do this with COUNTIF, but have never used it before and would like to request some guidance if possible.

The system info is as follows:

I am using Excel in Microsoft365 Version 2102 (Build 13801.20864) on a Windows 10 PC. I hope that is the info that is required/helps.

(Very simplified) Data sample:
Column A      Column B
10/05/2015    ID005
14/12/2018     ID112
08/09/2021     ID005

That's obviously quite a small sample as it goes on for multiple pages.

What I'm hoping to be able to do is list the ID and then the number of instances it occurs in each year (with the additional but not necessary hope that this will continue to be valid no matter how many additions get made to the table in the future). Is this possible?

Thank you for your time reading this. Please let me know if it isn't clear or requires further clarification.

My apologies if this seems a too simple task for the knowledge on here. Any help at all would be greatly appreciated.

Kind regards
JD

[#2 · Accepted solution]

@JohnD76 

There are several ways to do that

I would recommend you using Tables and Pivot Table.

First converting your range into an Excel Table.

Them applying a pivot table.

Another solution is using formulas with your range as Table.

=SUMPRODUCT((YEAR(MyData[Date])=H5)*(MyData[ID'#]=G5))

Find attached an example.

[#3]

Thank you Juliano-Petrukio!! I really appreciate you spending your time to help me out on that.

I'll let you know how it goes.

Cheers
JD

[#4]

Boss was extremely happy with the result! Thank you again!!

Kind regards
JD

[#5]

Anytime my friend.