# Count Multiples based on data

#### Grandma8

##### Board Regular
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>

### Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

#### kieran

##### Active Member
Hi Grandma8,

=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

##### Board Regular
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

##### Board Regular
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.

Replies
1
Views
452
Replies
6
Views
239
Replies
5
Views
275
Replies
12
Views
801
Replies
4
Views
505

1,190,817
Messages
5,983,066
Members
439,818
Latest member
schizoid231

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

### Which adblocker are you using?

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

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