Unique Item Count with Multiple Criteria in VBA

[pbornemeier]

I have a worksheet listing about 35000 jobs that were done over 4 years. Layout is start date in column A, finish data in column B and the supervisor in column C. I defined dynamic named ranges to cover each column. Start = KO_AS, Finish = KO_AF, Supervisor = KO_SUPERVISOR.
KO_SUPERVISOR_START is the named range of the first cell in the Supervisor column.

I want to create another worksheet with a range of dates in column A that covers the earliest start to latest finish dates from the previous; Column C to contain the count of jobs that were active on that day; column D to contain the number jobs that did not have a listed supervisor assigned and Column E to contain the number of non-blank supervisors that had jobs active that day.

The formulas I worked out for these columns are:

C2 =SUMPRODUCT(--(KO_AS<='Sheet1'!A2),--(KO_AF>='Sheet1'!A2))
D2 =SUMPRODUCT(--(KO_AS<='Sheet1'!A2),--(KO_AF>='Sheet1'!A2),--(KO_SUPERVISOR=""))

I found this post http://www.mrexcel.com/forum/showthread.php?t=373877 that helped me create a formula (which I still do not fully understand, but that appears to work) to calculate column E (formula is confirmed with CSE):

E2 =SUM(IF(FREQUENCY(IF(KO_AS<=A1,IF(KO_AF>=A1,IF(KO_SUPERVISOR<>"",MATCH("~"&KO_SUPERVISOR,KO_SUPERVISOR&"",0)))),ROW(KO_SUPERVISOR)-ROW(KO_SUPERVISOR_START)+1),1))

All formulas are copied down the date range of dates.

The CSE formula works, but it is very slow, when I use it (on ~1500 dates and 35000 jobs). Is there a faster solution for the column E data using VBA?

 

[pbornemeier]

E2 formula is actually:

E2 =SUM(IF(FREQUENCY(IF(KO_AS<=A2,IF(KO_AF>=A2,IF(KO_SUPERVISOR<>"",MATCH("~"&KO_SUPERVISOR,KO_SUPERVISOR&"",0)))),ROW(KO_SUPERVISOR)-ROW(KO_SUPERVISOR_START)+1),1))