EXCEL DATE - If Series Contains Date, the Count!

[bevo993071]

First off, thank you for your help and taking the time to read this. Here is the situation I am trying to solve: I have two date/time stamps per transport unique identifier, I am trying to write a formula that says if on a given date, how many active transports occurred that were not outsourced? Below is an example of what a finished product would look like.[TABLE="class: grid, width: 700, align: center"]
<tbody>[TR]
[TD="align: center"]Activated[/TD]
[TD="align: center"]Deactivated[/TD]
[TD="align: center"]Outcome[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Date[/TD]
[TD="align: center"]Utilization[/TD]
[/TR]
[TR]
[TD]07/28/2014 08:23[/TD]
[TD]07/28/2014 17:09[/TD]
[TD]Completed[/TD]
[TD][/TD]
[TD]07/28/2014[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]07/28/2014 08:01[/TD]
[TD]07/28/2014 16:06[/TD]
[TD]Completed[/TD]
[TD][/TD]
[TD]07/29/2014[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]08/02/2014 13:37[/TD]
[TD]08/02/2014 21:55[/TD]
[TD]Completed[/TD]
[TD][/TD]
[TD]07/30/2014[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]08/02/2014 22:20[/TD]
[TD]08/03/2014 01:32[/TD]
[TD]Completed[/TD]
[TD][/TD]
[TD]07/31/2014[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]08/04/2014 18:32[/TD]
[TD]08/04/2014 22:55[/TD]
[TD]Outsourced[/TD]
[TD][/TD]
[TD]08/01/2014[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]08/02/2014[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]08/03/2014[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]08/04/2014[/TD]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]


Thank you in advance for your help

 

[James006]

Hi,

In cell F2 for Utilization, you could test : =SUMPRODUCT((INT($A$2:$A$6)>=E2)*(INT($B$2:$B$6)<=E2)*($C$2:$C$6<>"Outsourced"))

HTH