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

[vij]

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”

alarm_happened_day	alarm_happened_time_gmt	alarm_respond_day	alarm_respond_time_gmt	alarm_type	response	Ack Time	Inv Time
01-07-2013	00:28:34	01-07-2013	00:32:04	Alarm Door..	INVESTIGATION - Began		00:03:30
04-07-2013	08:57:06	04-07-2013	08:57:22	Alarm Door..	INVESTIGATION - Began		00:00:16
05-07-2013	03:34:31	05-07-2013	03:38:02	Alarm Door..	INVESTIGATION - Began		00:03:31
05-07-2013	12:29:26	05-07-2013	12:29:58	Fire Alarm - Alarm Sounding	ACKNOWLEDGED P1 (Urgent) - Investigate Immediately	00:00:32
06-07-2013	08:30:02	06-07-2013	08:34:29	Alarm Door..	INVESTIGATION - Began		00:04:27
06-07-2013	08:47:46	06-07-2013	08:49:13	Alarm Door..	INVESTIGATION - Began		00:01:27
06-07-2013	11:08:07	06-07-2013	11:09:35	Alarm Door..	INVESTIGATION - Began		00:01:28
09-07-2013	09:08:30	09-07-2013	09:10:24	Alarm Door..	INVESTIGATION - Began opened by guard		00:01:54
10-07-2013	00:50:54	10-07-2013	00:51:08	Alarm Door..	INVESTIGATION - Began		00:00:14
11-07-2013	00:22:27	11-07-2013	00:22:43	Alarm Door..	INVESTIGATION - Began		00:00:16
11-07-2013	13:45:14	11-07-2013	13:45:21	Fire Alarm - Alarm Sounding	Acknowledge P1 (Urgent) - Investigate Immediately	00:00:32
12-07-2013	00:39:17	12-07-2013	00:43:46	Alarm Door..	INVESTIGATION - Began		00:04:29
12-07-2013	13:35:40	12-07-2013	13:42:31	Alarm Door..	INVESTIGATION - Began		00:06:51
12-07-2013	13:38:24	12-07-2013	13:42:34	Alarm Door..	INVESTIGATION - Began		00:04:10
12-07-2013	15:03:20	12-07-2013	15:03:36	Alarm Door..	INVESTIGATION - Began		00:00:16
12-07-2013	15:19:37	12-07-2013	15:23:04	Alarm Door..	INVESTIGATION - Began		00:03:27
12-07-2013	15:19:46	12-07-2013	15:26:04	Alarm Door..	INVESTIGATION - Began		00:06:18
13-07-2013	01:02:58	13-07-2013	01:03:14	Alarm Door..	INVESTIGATION - Began		00:00:16
13-07-2013	06:04:01	13-07-2013	06:05:16	Alarm Door..	INVESTIGATION - Began		00:01:15
13-07-2013	06:34:43	13-07-2013	06:35:11	Alarm Door..	ACKNOWLEDGED P2 (High) - Investigate within 2:00 minutes	00:00:28
13-07-2013	06:36:09	13-07-2013	06:36:43	Alarm Door..	ACKNOWLEDGED P2 (High) - Investigate within 2:00 minutes	00:02:34
14-07-2013	18:17:27	14-07-2013	18:21:19	Fire Alarm - Alarm Sounding	ACKNOWLEDGED P1 (Urgent) - Investigate Immediately	00:03:52
14-07-2013	18:17:27	14-07-2013	18:21:33	Fire Alarm - Alarm Sounding	INVESTIGATION - Began		00:04:06
15-07-2013	06:36:56	15-07-2013	06:37:10	Alarm Door..	INVESTIGATION - Began		00:00:14
15-07-2013	08:14:21	15-07-2013	08:14:37	Alarm Door..	INVESTIGATION - Began		00:00:16
14-07-2013	18:27:27	14-07-2013	18:27:33	Fire Alarm - Alarm Sounding	INVESTIGATION - Began		00:00:06
14-07-2013	18:37:27	14-07-2013	18:39:33	Fire Alarm - Alarm Sounding	INVESTIGATION - Began		00:02:06
14-07-2013	18:47:27	14-07-2013	18:48:33	Fire Alarm - Alarm Sounding	INVESTIGATION - Began		00:01:06

<tbody>
</tbody>

Thanks,
Vijay

 

[QU4487]

Try this;

=COUNTIFS(E:E,I1,G:G,">0.00104166666666667")

I1 = Cell with the Alarm type you're looking for. This could be replaced by “Fire Alarm - Alarm Sounding", if you preferred.

 

[vij]

Thanks QU4487..That was really helpful.