Forum Discussion

JohnD76's avatar
JohnD76
Copper Contributor
Sep 14, 2021
Solved

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

  • 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.

4 Replies

  • 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.

    • JohnD76's avatar
      JohnD76
      Copper Contributor
      Boss was extremely happy with the result! Thank you again!!

      Kind regards
      JD
    • JohnD76's avatar
      JohnD76
      Copper Contributor
      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

Resources