Count number of time a time value increases for a particular condition

  • Hi,
    Require your help with some excel formulae.
    In Worksheet “data sheet” under the Ack time(Column G ) I want to count all the instances when the time is more than 00:01:30 (more than 90 seconds)for a particular type of Alarm_Type (Column E ).


    Just as an example for Alarm_type “Fire Alarm - Alarm Sounding” the count is “01” and under column Inv_Time (Column H) is “02”



    [TABLE="width: 956"]

    [tr]


    [td]

    alarm_happened_day

    [/td]


    [td]

    alarm_happened_time_gmt

    [/td]


    [td]

    alarm_respond_day

    [/td]


    [td]

    alarm_respond_time_gmt

    [/td]


    [td]

    alarm_type

    [/td]


    [td]

    response

    [/td]


    [td]

    Ack Time

    [/td]


    [td]

    Inv Time

    [/td]


    [/tr]


    [tr]


    [TD="align: right"]01-07-2013[/TD]
    [TD="align: right"]00:28:34[/TD]
    [TD="align: right"]01-07-2013[/TD]
    [TD="align: right"]00:32:04[/TD]

    [td]

    Alarm Door..

    [/td]


    [td]

    INVESTIGATION - Began

    [/td]


    [td][/td]


    [TD="align: right"]00:03:30[/TD]

    [/tr]


    [tr]


    [TD="align: right"]04-07-2013[/TD]
    [TD="align: right"]08:57:06[/TD]
    [TD="align: right"]04-07-2013[/TD]
    [TD="align: right"]08:57:22[/TD]

    [td]

    Alarm Door..

    [/td]


    [td]

    INVESTIGATION - Began

    [/td]


    [td][/td]


    [TD="align: right"]00:00:16[/TD]

    [/tr]


    [tr]


    [TD="align: right"]05-07-2013[/TD]
    [TD="align: right"]03:34:31[/TD]
    [TD="align: right"]05-07-2013[/TD]
    [TD="align: right"]03:38:02[/TD]

    [td]

    Alarm Door..

    [/td]


    [td]

    INVESTIGATION - Began

    [/td]


    [td][/td]


    [TD="align: right"]00:03:31[/TD]

    [/tr]


    [tr]


    [TD="align: right"]05-07-2013[/TD]
    [TD="align: right"]12:29:26[/TD]
    [TD="align: right"]05-07-2013[/TD]
    [TD="align: right"]12:29:58[/TD]

    [td]

    Fire Alarm - Alarm Sounding

    [/td]


    [td]

    ACKNOWLEDGED P1 (Urgent) - Investigate Immediately

    [/td]


    [TD="align: right"]00:00:32[/TD]

    [td][/td]


    [/tr]


    [tr]


    [TD="align: right"]06-07-2013[/TD]
    [TD="align: right"]08:30:02[/TD]
    [TD="align: right"]06-07-2013[/TD]
    [TD="align: right"]08:34:29[/TD]

    [td]

    Alarm Door..

    [/td]


    [td]

    INVESTIGATION - Began

    [/td]


    [td][/td]


    [TD="align: right"]00:04:27[/TD]

    [/tr]


    [tr]


    [TD="align: right"]06-07-2013[/TD]
    [TD="align: right"]08:47:46[/TD]
    [TD="align: right"]06-07-2013[/TD]
    [TD="align: right"]08:49:13[/TD]

    [td]

    Alarm Door..

    [/td]


    [td]

    INVESTIGATION - Began

    [/td]


    [td][/td]


    [TD="align: right"]00:01:27[/TD]

    [/tr]


    [tr]


    [TD="align: right"]06-07-2013[/TD]
    [TD="align: right"]11:08:07[/TD]
    [TD="align: right"]06-07-2013[/TD]
    [TD="align: right"]11:09:35[/TD]

    [td]

    Alarm Door..

    [/td]


    [td]

    INVESTIGATION - Began

    [/td]


    [td][/td]


    [TD="align: right"]00:01:28[/TD]

    [/tr]


    [tr]


    [TD="align: right"]09-07-2013[/TD]
    [TD="align: right"]09:08:30[/TD]
    [TD="align: right"]09-07-2013[/TD]
    [TD="align: right"]09:10:24[/TD]

    [td]

    Alarm Door..

    [/td]


    [td]


    INVESTIGATION - Began
    opened by guard

    [/td]


    [td][/td]


    [TD="align: right"]00:01:54[/TD]

    [/tr]


    [tr]


    [TD="align: right"]10-07-2013[/TD]
    [TD="align: right"]00:50:54[/TD]
    [TD="align: right"]10-07-2013[/TD]
    [TD="align: right"]00:51:08[/TD]

    [td]

    Alarm Door..

    [/td]


    [td]

    INVESTIGATION - Began

    [/td]


    [td][/td]


    [TD="align: right"]00:00:14[/TD]

    [/tr]


    [tr]


    [TD="align: right"]11-07-2013[/TD]
    [TD="align: right"]00:22:27[/TD]
    [TD="align: right"]11-07-2013[/TD]
    [TD="align: right"]00:22:43[/TD]

    [td]

    Alarm Door..

    [/td]


    [td]

    INVESTIGATION - Began

    [/td]


    [td][/td]


    [TD="align: right"]00:00:16[/TD]

    [/tr]


    [tr]


    [TD="align: right"]11-07-2013[/TD]
    [TD="align: right"]13:45:14[/TD]
    [TD="align: right"]11-07-2013[/TD]
    [TD="align: right"]13:45:21[/TD]

    [td]

    Fire Alarm - Alarm Sounding

    [/td]


    [td]

    Acknowledge P1 (Urgent) - Investigate Immediately

    [/td]


    [TD="align: right"]00:00:32[/TD]

    [td][/td]


    [/tr]


    [tr]


    [TD="align: right"]12-07-2013[/TD]
    [TD="align: right"]00:39:17[/TD]
    [TD="align: right"]12-07-2013[/TD]
    [TD="align: right"]00:43:46[/TD]

    [td]

    Alarm Door..

    [/td]


    [td]

    INVESTIGATION - Began

    [/td]


    [td][/td]


    [TD="align: right"]00:04:29[/TD]

    [/tr]


    [tr]


    [TD="align: right"]12-07-2013[/TD]
    [TD="align: right"]13:35:40[/TD]
    [TD="align: right"]12-07-2013[/TD]
    [TD="align: right"]13:42:31[/TD]

    [td]

    Alarm Door..

    [/td]


    [td]

    INVESTIGATION - Began

    [/td]


    [td][/td]


    [TD="align: right"]00:06:51[/TD]

    [/tr]


    [tr]


    [TD="align: right"]12-07-2013[/TD]
    [TD="align: right"]13:38:24[/TD]
    [TD="align: right"]12-07-2013[/TD]
    [TD="align: right"]13:42:34[/TD]

    [td]

    Alarm Door..

    [/td]


    [td]

    INVESTIGATION - Began

    [/td]


    [td][/td]


    [TD="align: right"]00:04:10[/TD]

    [/tr]


    [tr]


    [TD="align: right"]12-07-2013[/TD]
    [TD="align: right"]15:03:20[/TD]
    [TD="align: right"]12-07-2013[/TD]
    [TD="align: right"]15:03:36[/TD]

    [td]

    Alarm Door..

    [/td]


    [td]

    INVESTIGATION - Began

    [/td]


    [td][/td]


    [TD="align: right"]00:00:16[/TD]

    [/tr]


    [tr]


    [TD="align: right"]12-07-2013[/TD]
    [TD="align: right"]15:19:37[/TD]
    [TD="align: right"]12-07-2013[/TD]
    [TD="align: right"]15:23:04[/TD]

    [td]

    Alarm Door..

    [/td]


    [td]

    INVESTIGATION - Began

    [/td]


    [td][/td]


    [TD="align: right"]00:03:27[/TD]

    [/tr]


    [tr]


    [TD="align: right"]12-07-2013[/TD]
    [TD="align: right"]15:19:46[/TD]
    [TD="align: right"]12-07-2013[/TD]
    [TD="align: right"]15:26:04[/TD]

    [td]

    Alarm Door..

    [/td]


    [td]

    INVESTIGATION - Began

    [/td]


    [td][/td]


    [TD="align: right"]00:06:18[/TD]

    [/tr]


    [tr]


    [TD="align: right"]13-07-2013[/TD]
    [TD="align: right"]01:02:58[/TD]
    [TD="align: right"]13-07-2013[/TD]
    [TD="align: right"]01:03:14[/TD]

    [td]

    Alarm Door..

    [/td]


    [td]

    INVESTIGATION - Began

    [/td]


    [td][/td]


    [TD="align: right"]00:00:16[/TD]

    [/tr]


    [tr]


    [TD="align: right"]13-07-2013[/TD]
    [TD="align: right"]06:04:01[/TD]
    [TD="align: right"]13-07-2013[/TD]
    [TD="align: right"]06:05:16[/TD]

    [td]

    Alarm Door..

    [/td]


    [td]

    INVESTIGATION - Began

    [/td]


    [td][/td]


    [TD="align: right"]00:01:15[/TD]

    [/tr]


    [tr]


    [TD="align: right"]13-07-2013[/TD]
    [TD="align: right"]06:34:43[/TD]
    [TD="align: right"]13-07-2013[/TD]
    [TD="align: right"]06:35:11[/TD]

    [td]

    Alarm Door..

    [/td]


    [td]

    ACKNOWLEDGED P2 (High) - Investigate within 2:00 minutes

    [/td]


    [TD="align: right"]00:00:28[/TD]

    [td][/td]


    [/tr]


    [tr]


    [TD="align: right"]13-07-2013[/TD]
    [TD="align: right"]06:36:09[/TD]
    [TD="align: right"]13-07-2013[/TD]
    [TD="align: right"]06:36:43[/TD]

    [td]

    Alarm Door..

    [/td]


    [td]

    ACKNOWLEDGED P2 (High) - Investigate within 2:00 minutes

    [/td]


    [TD="align: right"]00:02:34[/TD]

    [td][/td]


    [/tr]


    [tr]


    [TD="align: right"]14-07-2013[/TD]
    [TD="align: right"]18:17:27[/TD]
    [TD="align: right"]14-07-2013[/TD]
    [TD="align: right"]18:21:19[/TD]

    [td]

    Fire Alarm - Alarm Sounding

    [/td]


    [td]

    ACKNOWLEDGED P1 (Urgent) - Investigate Immediately

    [/td]


    [TD="align: right"]00:03:52[/TD]

    [td][/td]


    [/tr]


    [tr]


    [TD="align: right"]14-07-2013[/TD]
    [TD="align: right"]18:17:27[/TD]
    [TD="align: right"]14-07-2013[/TD]
    [TD="align: right"]18:21:33[/TD]

    [td]

    Fire Alarm - Alarm Sounding

    [/td]


    [td]

    INVESTIGATION - Began

    [/td]


    [td][/td]


    [TD="align: right"]00:04:06[/TD]

    [/tr]


    [tr]


    [TD="align: right"]15-07-2013[/TD]
    [TD="align: right"]06:36:56[/TD]
    [TD="align: right"]15-07-2013[/TD]
    [TD="align: right"]06:37:10[/TD]

    [td]

    Alarm Door..

    [/td]


    [td]

    INVESTIGATION - Began

    [/td]


    [td][/td]


    [TD="align: right"]00:00:14[/TD]

    [/tr]


    [tr]


    [TD="align: right"]15-07-2013[/TD]
    [TD="align: right"]08:14:21[/TD]
    [TD="align: right"]15-07-2013[/TD]
    [TD="align: right"]08:14:37[/TD]

    [td]

    Alarm Door..

    [/td]


    [td]

    INVESTIGATION - Began

    [/td]


    [td][/td]


    [TD="align: right"]00:00:16[/TD]

    [/tr]


    [tr]


    [TD="align: right"]14-07-2013[/TD]
    [TD="align: right"]18:27:27[/TD]
    [TD="align: right"]14-07-2013[/TD]
    [TD="align: right"]18:27:33[/TD]

    [td]

    Fire Alarm - Alarm Sounding

    [/td]


    [td]

    INVESTIGATION - Began

    [/td]


    [td][/td]


    [TD="align: right"]00:00:06[/TD]

    [/tr]


    [tr]


    [TD="align: right"]14-07-2013[/TD]
    [TD="align: right"]18:37:27[/TD]
    [TD="align: right"]14-07-2013[/TD]
    [TD="align: right"]18:39:33[/TD]

    [td]

    Fire Alarm - Alarm Sounding

    [/td]


    [td]

    INVESTIGATION - Began

    [/td]


    [td][/td]


    [TD="align: right"]00:02:06[/TD]

    [/tr]


    [tr]


    [TD="align: right"]14-07-2013[/TD]
    [TD="align: right"]18:47:27[/TD]
    [TD="align: right"]14-07-2013[/TD]
    [TD="align: right"]18:48:33[/TD]

    [td]

    Fire Alarm - Alarm Sounding

    [/td]


    [td]

    INVESTIGATION - Began

    [/td]


    [td][/td]


    [TD="align: right"]00:01:06




    [/TD]

    [/tr]


    [/TABLE]
    Thanks,
    Vijay

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!