conditional counting

[manish Joshi07]

Hi,

I need support to find out the formula to solve the issue.conditions are

1)Count required of D column
2)If D value repeate with in 20 min(With ref to Start time) consider as 1 instance only and after 20 min as another instance

I have done manually in last column.




EventStartDate Start Time EndTime Name example
25-Jul-15 17:22 17:26 ABJ 1
25-Jul-15 17:54 17:58 AMB 1
25-Jul-15 15:58 16:05 ANK 1
25-Jul-15 13:28 13:34 ANT 1
25-Jul-15 15:58 16:07 ATO 1
25-Jul-15 12:47 12:50 BEA 1
25-Jul-15 12:57 13:04 BEA 0
25-Jul-15 20:43 20:44 BHO 1
25-Jul-15 20:44 20:50 BHO 0
25-Jul-15 20:43 20:44 BKW 1
25-Jul-15 22:17 22:19 BNY 1
25-Jul-15 17:22 17:24 HJO 1
25-Jul-15 16:22 16:24 JST 1
25-Jul-15 13:13 13:15 KDY 1
25-Jul-15 13:48 13:51 KDY 0

[Jacc]

Something like this? Arrayformula, it has to be entered with Ctrl + Shift + Enter.

Please Login or Register  to view this content.

[samba_ravi]

will you please attach sample excel file with expected result

[manish Joshi07]

Hi,

Pl find the attachment.

[samba_ravi]

manish Joshi07, Why E16=0

[manish Joshi07]

u r right it is 1

[samba_ravi]

=--(COUNTIFS($D$2:$D$16,$D2,$A$2:$A$16,$A2,$B$2:$B$16,"<"&$B2,$B$2:$B$16,">"&$B2-TIME(0,20,0))=0)

Please Login or Register  to view this content.

Try this and copy towards down

[samba_ravi]

or
=IF(D2<>D1,1,IF(B2-B1<=TIME(0,20,0),0,1))

Please Login or Register  to view this content.

[manish Joshi07]

thanks it working.