Multiple Matches

[healie2204]

Hi,
After previously having a huge amount of help via this forum previously I’m hoping one of you excel gods wouldn’t mind looking at another thing that’s got me stumped.

So I’m trying to identify where a telephone call has been made by an agent during a time period they weren’t supposed to be active. This is for a period of 3 months and the data I have is two tables, one holding the names of the agents and the date each call was made. The second the names of the agents and a start and end date of an ‘off-task’ period.

The Data tables are huge (200,000 rows of calls and 1,800 rows of off-task periods).

Below are examples of the data tables I’m working with, in Column D of the second image I need a count of how many calls (Column B on the first image) fall between the start and end dates (Col B/C second image) for each agent.

I’m hoping this might be relatively straight forward for someone skilled in excel to figure out.
Thanks I’m advance,
H

 

[jasonb75]

Assuming that the top image is of Sheet1, edit as necessary.

=COUNTIFS(Sheet1!$A:$A,$A2,Sheet1!$B:$B,">="&$B2,Sheet1!$B:$B,"<="&$C2)

Please remember to use xl2bb for posting samples, not screen captures.

 

[healie2204]

Assuming that the top image is of Sheet1, edit as necessary.

=COUNTIFS(Sheet1!$A:$A,$A2,Sheet1!$B:$B,">="&$B2,Sheet1!$B:$B,"<="&$C2)

Please remember to use xl2bb for posting samples, not screen captures.

Hi jasonb75, thanks for this. Apologies forthe lack of xl2bb, I’ve only currently got access to a mobile device so wasn’t sure if it was an option. Thanks again

 

[jasonb75]

You're welcome

I can't say for certain as I only use excel on desktop, but I believe that mobile versions have limited functionality that would not allow for the use of the add in.