Forum Discussion
Summing the number of instances of an item in each year
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
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.
4 Replies
- Juliano-PetrukioBronze Contributor
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.
- JohnD76Copper ContributorBoss was extremely happy with the result! Thank you again!!
Kind regards
JD- Juliano-PetrukioBronze ContributorAnytime my friend.
- JohnD76Copper ContributorThank 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