Count Multiples based on data

[Grandma8]

Data below shows and ID number next to the year of participation in our program. The goal is to find out the number of individuals that participated in all three years (2014, 2015, 2016). I can do this manually but prefer using a formula. I do not know VBA, macros, etc. so I am hoping that a formula is possible. The data below shows one person participated in all three years. The list I am working with has about 90 names. Please advise. Thank you.

Sample Data

ID	Year
1059	2014
1059	2015
1106	2014
1191	2015
1191	2016
1216	2014
1240	2015
1275	2014
1275	2015
1275	2016

<tbody>
</tbody>

 

[kieran]

Hi Grandma8,

Assuming your data is in the range A2:B90, add the formula in an adjacent column.
=COUNTIF($A$2:$A$90,A2)
This will give you a count in each row of the number of times that an ID is present in the list. You can then use autofilter to reduce the list to only those with 3 years, one row will show per year for each ID.

Another quick method is to create a pivot table of the data, with the ID in the rows, and the year in the columns. This will provide a quick summary of the participation across years, with an indicator by column for each year. Let me know if you need assistance with this.

 

[Grandma8]

The countif formula does give me a view where I can easily see how many "3s" there are which indicates participation in all three years. I am unfamiliar with Pivot tables. Since column A has duplication of numbers, will a pivot table only show the ID number once and then populate the appropriate years in the column? Can you provide me the best source of instruction for learning pivot tables?

 

[Grandma8]

I was able to get started with the pivot table but the data is not correct. Also, the columns are not sorting in numeric order. Can I share the data with you? If so, what is the best way for you to receive the file? Again, I am not savvy in this area.