Count unique dates within given period with multiple criteria

[infinitedrifter]

I know there are quite a few post on this but I haven't been able to get any of the solutions to work.


I have a table of call logs w/ the employee extensions (customer time[extensions]) in one column and dates (customer_time[date int]) in another (converted to an integer so the time data is scrubbed). I have a start date (mtd_start) and end date (mtd_end) and need to calculate the number of days each extension was logged in making outbound calls within the given period, which should equal the number of days worked for that employee.


The table is already about 5500 lines for about 4 months (in a large workbook), so I’m trying to make the formula as low processor-intensive as I can.


Any suggestions?


https://www.dropbox.com/s/6v1uxz281ej2625/days%20worked%20sample.xlsx


Thanks

 

[Faseeh]

Hi infinitedrifter,


please try following in L2:

=COUNTIFS(customer_time[Extensions],L1,customer_time[Date],">="&mtd_start,customer_time[Date],"<="&customer_time[Date])

or if it does't work please provide the correct answers for L2:M2.


Regards,

 

[infinitedrifter]

Thanks Faseeh. This is a good start, but I think it counts the total number of dates that meet the criteria but doesn't narrow it down to unique dates. I updated the example doc w/ a pivot table that I believe calculates the correct answers (11 for L2, 8 for M2, 0 for N2). Is there a way to count the unique dates (without using a pivot table)?

 

[NARAYANK991]

Hi ,


This is a standard technique :


=SUM(IF((customer_time[Extensions]=L$1)*(customer_time[Date Int]>=mtd_start)*(customer_time[Date Int]<=mtd_end),1/COUNTIFS(customer_time[Extensions],L$1,customer_time[Date Int],">="&mtd_start,customer_time[Date Int],"<="&mtd_end,customer_time[Date Int],customer_time[Date Int])))


where the IF contains a multiplication of the various criteria , the COUNTIFS contains the criteria , and the column whose unique counts are required , is repeated.


This is an array formula , to be entered using CTRL SHIFT ENTER.


Copy this across.


Narayan

 

[infinitedrifter]

Thanks Narayan, this is exactly what I needed. I expanded it to include [Counted]:


=SUM(IF((customer_time[Extensions]=M$1)*(customer_time[Counted]=1)*(customer_time[Date Int]>=mtd_start)*(customer_time[Date Int]<=mtd_end),1/COUNTIFS(customer_time[Extensions],M$1,customer_time[Counted],1,customer_time[Date Int],">="&mtd_start,customer_time[Date Int],"<="&mtd_end,customer_time[Date Int],customer_time[Date Int])))


It calculates perfectly, but slows processing down to about 20 seconds. Do you know of a way to do this with VBA?


Thanks again, I've been trying to do something similar for months & you knocked it out like a true ninja.

 

[NARAYANK991]

Hi ,


It can certainly be done using VBA ; hopefully it should be faster.


Give me some time , and I'll get back to you.


Narayan