There are two columns in the data table, one includes the date and another one includes name like the one shown below. There is also a data table, which is a list of date. I would like to pick a name, let's say Tom, and count the no. of dates corresponding to Tom in the dates listed in the data table. To be specific, Tom appears on 2/4 for one time, 27/4 for two times, 1/4 for two times. However, 1/4 is not on the list, so it is not counted. There are two distinct dates left which are 2/4 and 27/4, so the outcome should be 2. I would really appreciate if someone can help me to figure out the formula, I have spent hours on it, thanks!!!
Data Table:
<colgroup><col><col></colgroup><tbody>
</tbody>
P.S. There are blank rows in between the table, which can be deleted if necessary.
Matching Table:
<colgroup><col></colgroup><tbody>
</tbody>
Meet 2 criteria:
1. the date listed on the matching table
2. The assigned name
Data Table:
2/4/2018 | Tom |
3/4/2018 | Mary |
4/4/2018 | Jason |
26/4/2018 | Sam |
27/4/2018 | Tom |
27/4/2018 | Tom |
30/4/2018 | Sam |
1/4/2018 | Tom |
1/4/2018 | Tom |
<colgroup><col><col></colgroup><tbody>
</tbody>
P.S. There are blank rows in between the table, which can be deleted if necessary.
Matching Table:
2/4/2018 |
3/4/2018 |
4/4/2018 |
5/4/2018 |
6/4/2018 27/4/2018 |
<colgroup><col></colgroup><tbody>
</tbody>
Meet 2 criteria:
1. the date listed on the matching table
2. The assigned name