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

V

vij

Board Regular
Joined
Feb 13, 2011
Messages
215
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="class: cms_table, width: 956"]
<tbody>[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]
</tbody>[/TABLE]

Thanks,
Vijay
 
Sort by date Sort by votes
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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Solved
Help Please
2 3
Replies
21
Views
700
Asbestos_Jen
Asbestos_Jen
A
  • Solved
Count entry by time
Replies
3
Views
330
admvlad
A
D
  • Question Question
Formula help needed for working with days and times.
Replies
0
Views
535
dshtesy
D
C
  • Solved
Calculate hour total for each week
2
Replies
16
Views
283
CaptainGravyBum
C
B
  • Question Question
Excel Pivot Table to see the number of "Lost Calls"
Replies
0
Views
107
Bernie Galve
B

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top