Hi, I have two lists and want to have a formula that I can use as the COUNT of the records of two employee lists to see what names are in both then to use this count for data to extract relevant records and also between two date ranges. The formula for count I am using is =SUMPRODUCT(--ISNUMBER(MATCH(B2:B28,E2,E11,0)/COUNTIFS(A2:A28,">="&I2 (Start Date), A2:A28,"<="&J2 (End Date) but don't think this correct.
I finally need to enter a formula in the first cell under date of the empty grid, which I think maybe similar to IF(ROWS(E$17:E17)>COUNT,"",INDEX(Date Range), AGGREGATE(15,6,(ROW(Date Range)-ROW(A2)+1)/ISNUMBER(MATCH(Employee First Range (Column B), Employee Second Range (Column E),0)*(Date Range >=Start Date)*(Date Range<=End Date),ROWS(E$17:E17) but not sure on this. The info is below;
Employee List 1
Employee List 2
Many Thanks
Nick
I finally need to enter a formula in the first cell under date of the empty grid, which I think maybe similar to IF(ROWS(E$17:E17)>COUNT,"",INDEX(Date Range), AGGREGATE(15,6,(ROW(Date Range)-ROW(A2)+1)/ISNUMBER(MATCH(Employee First Range (Column B), Employee Second Range (Column E),0)*(Date Range >=Start Date)*(Date Range<=End Date),ROWS(E$17:E17) but not sure on this. The info is below;
Employee List 1
| Date | Employee | Contacts Made |
01/07/2024 | Nick | 75 |
08/07/2024 | Dave | 80 |
15/07/2024 | Helen | 85 |
22/07/2024 | Paul | 90 |
29/07/2024 | Tom | 95 |
05/08/2024 | James | 100 |
12/08/2024 | Alex | 105 |
19/08/2024 | Carolyn | 110 |
26/08/2024 | Nick | 115 |
02/09/2024 | Alex | 120 |
09/09/2024 | James | 125 |
16/09/2024 | Helen | 130 |
23/09/2024 | Nick | 135 |
30/09/2024 | James | 140 |
Employee List 2
| Employee |
| Gary |
| James |
| Terry |
| Fiona |
| Jason |
| Paula |
| Nick |
| Tim |
| Scott |
| Mandy |
| Count | Start Date | End Date | |
6 | 01/07/2024 | 09/09/2024 |
| Date | Employee | Contacts Made |
| IF(ROWS Formula | Copied Over | Copied Over |
Many Thanks
Nick
