I need a formula that will be able to count the number of days that a persons name appears in a large data set within a defined date range. The issue I am having is that the persons name will likely have multiple entries on the same day, and i only want to count the first instance. Any help is greatly appreciated.
An example data set is below. I want to determine formula in G7:G10 to count how many days the people listed in F7:F10 have an entry in the dataset A4:C25, within the date range in G1:G2.
<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
An example data set is below. I want to determine formula in G7:G10 to count how many days the people listed in F7:F10 have an entry in the dataset A4:C25, within the date range in G1:G2.
A | B | C | D | E | F | G | |
---|---|---|---|---|---|---|---|
1 | Start date | 11/1/2018 | |||||
2 | End Date | 2/17/2018 | |||||
3 | Date | Person | Code | ||||
4 | 15-Feb | Bob | 100 | ||||
5 | 15-Feb | Bob | 110 | ||||
6 | 15-Feb | Bob | 300 | Days with an entry | |||
7 | 15-Feb | Frank | 500 | Bob | 3 | ||
8 | 15-Feb | Frank | 100 | Frank | 2 | ||
9 | 15-Feb | Frank | 200 | Bill | 2 | ||
10 | 15-Feb | Bill | 250 | Mike | 0 | ||
11 | 16-Feb | Bob | 100 | ||||
12 | 16-Feb | Bob | 110 | ||||
13 | 16-Feb | Bob | 300 | ||||
14 | 16-Feb | Bob | 310 | ||||
15 | 16-Feb | Bob | 320 | ||||
16 | 16-Feb | Bob | 200 | ||||
17 | 16-Feb | Bob | 210 | ||||
18 | 16-Feb | Frank | 500 | ||||
19 | 17-Feb | Bill | 100 | ||||
20 | 17-Feb | Bill | 110 | ||||
21 | 17-Feb | Bill | 300 | ||||
22 | 17-Feb | Bill | 210 | ||||
23 | 17-Feb | Bob | 300 | ||||
24 | 17-Feb | Bob | 210 | ||||
25 | 17-Feb | Bob | 100 |
<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet1