COUNTIFS Hours and minutes in range

[jao1310]

[TABLE="width: 500"]
<tbody>[TR]
[TD]Duration hours:mins
Column A
[/TD]
[TD]Type of interruption
Column B
[/TD]
[TD]Number of cust affected
Column C
[/TD]
[TD]Event day? Y/N
Column D
[/TD]
[/TR]
[TR]
[TD]13:39
[/TD]
[TD]Unplanned
[/TD]
[TD]13
[/TD]
[TD]N[/TD]
[/TR]
[TR]
[TD]43.27
[/TD]
[TD]Unplanned [/TD]
[TD]20
[/TD]
[TD]N[/TD]
[/TR]
[TR]
[TD]6:00
[/TD]
[TD]Planned
[/TD]
[TD]80
[/TD]
[TD]N
[/TD]
[/TR]
</tbody>[/TABLE]

Hi,

The above table shows a sample of what I am doing.
I'm trying to count the number of instances that meets the following criteria:

Number of instances where Unplanned interruption occrured during none event day that are: (the formula on right is what I currently have on the table but is not working. It only displays "0". I just want to note that the time column (Column A) is formatted as [h]:mm;@

[TABLE="width: 500"]
<tbody>[TR]
[TD]

<8 hours ​

<strike></strike>
[/TD]
[TD]=COUNTIFS($A$4:$A$47,"<8:00",$B$4:$B$47,"Unplanned",$D$4:$D$47,"NO")
[/TD]
[/TR]
[TR]
[TD]

>8 hours and <12 hours ​

<strike></strike>
[/TD]
[TD]COUNTIFS($A$4:$A$47,">8:00",$A$4:$A$47,"<12:00",$B$4:$B$47,"Unplanned",$D$4:$D$47,"NO")<strike></strike>
[/TD]
[/TR]
[TR]
[TD]

=12 hours ​

<strike></strike>
[/TD]
[TD]

=COUNTIFS($A$4:$A$47,"=12:00",$B$4:$B$47,"Unplanned",$D$4:$D$47,"NO")​

[/TD]
[/TR]
[TR]
[TD]

>12 hours and <=24 hours ​

<strike></strike>
[/TD]
[TD]

COUNTIFS($A$4:$A$47,">12:00",$A$4:$A$47,"<24:00",$B$4:$B$47,"Unplanned",$D$4:$D$47,"NO")​

<strike style="background-color: transparent; border-collapse: collapse; color: rgb(34, 34, 34); font-family: Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif; font-size: 13px; font-style: normal; font-variant: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: line-through; text-indent: 0px; text-transform: none; -webkit-text-stroke-width: 0px; white-space: normal; width: auto; word-spacing: 0px;"></strike>
[/TD]
[/TR]
[TR]
[TD]

36 to 48 hours ​

<strike></strike>
[/TD]
[TD]

COUNTIFS($A$4:$A$47,">36:00",$A$4:$A$47,"=48:00",$B$4:$B$47,"Unplanned",$D$4:$D$47,"NO")​

<strike style="background-color: transparent; border-collapse: collapse; color: rgb(34, 34, 34); font-family: Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif; font-size: 13px; font-style: normal; font-variant: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: line-through; text-indent: 0px; text-transform: none; -webkit-text-stroke-width: 0px; white-space: normal; width: auto; word-spacing: 0px;"></strike>
[/TD]
[/TR]
</tbody>[/TABLE]

Thank you.

Jao

 

[CyrusTheVirus]

Welcome to the forums.

1) What does Column C have to do with?
2) Where do you want the answers to be displayed?
3) In Column D is it "NO" or just "N"?
4) Can you please paste your whole set of data (or a bit more data), along with your expected results?

 

[jao1310]

Hi

Thanks heaps for replying.

1) Sorry, column C has nothing to do with it, so maybe just disregard that one.
2.) Answers are displayed below the table.

3) Column D is "NO" not N (Sorry, I was in a hurry yesterday that I put "N" on my sample table, however on my orginal table, answers in Column D are either a YES or NO

4) The table that I provided yesterday is just a sample on how I presented the table and the data. Basically, what I want to do is to COUNT the (Criteria 1) number of 'Unplanned interruptions' on a (Criteria 2) certain range of duration, and that are (Criteria 3) Not a Major event day (so we're looking at the 'NO' answers).
I have a feeling that the problem is how Excel is seeing Time (hours: minutes). I formatted Column A as [h]:mm;@

Column A Column B Column D
Duration of interrution Type of interruption Did this occur on a major event day? YES/NO

12:00 Planned NO
6:00 Unplanned NO
12:35 Unplanned NO
43:51 Unplanned NO
32:22 Planned YES
14:24 Unplanned YES
18:21 Unplanned NO

Expected results (I changed the hour ranges as the previous one is incorrect)

Number of Unplanned interruptions (excluding major event days)

Parameter answer
<8 hours [1]
>8 hours and <=12 hours [0]
=12 hours [1]
>12 hours and <=24 hours [1]
>24 hours <=48 hours [1] ​

Thank you again.

 

[CyrusTheVirus]

Will the below work? Copy/Paste the below into excel.

Criteria column cell G2 Formula:

=IF(ROWS($F$2:F2)=1,COUNTIFS($D$2:$D$8,"NO",$B$2:$B$8,"Unplanned",$A$2:$A$8,"<"&(F2/24)),COUNTIFS($D$2:$D$8,"NO",$B$2:$B$8,"Unplanned",$A$2:$A$8,">"&(F1/24),$A$2:$A$8,"<="&(F2/24)))

[TABLE="width: 651"]
<colgroup><col><col span="2"><col><col><col><col></colgroup><tbody>[TR]
[TD]Duration (Hrs:Min)[/TD]
[TD]Planned/Unplanned[/TD]
[TD]No. Of Cust Affected[/TD]
[TD]Event day?[/TD]
[TD][/TD]
[TD]Hours[/TD]
[TD]Criteria[/TD]
[/TR]
[TR]
[TD="align: right"]12:00[/TD]
[TD]Planned[/TD]
[TD]Blank (For Now)[/TD]
[TD]No[/TD]
[TD][/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]6:00[/TD]
[TD]Unplanned[/TD]
[TD]Blank (For Now)[/TD]
[TD]No[/TD]
[TD][/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]12:35[/TD]
[TD]Unplanned[/TD]
[TD]Blank (For Now)[/TD]
[TD]No[/TD]
[TD][/TD]
[TD="align: right"]24[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]43:51[/TD]
[TD]Unplanned[/TD]
[TD]Blank (For Now)[/TD]
[TD]No[/TD]
[TD][/TD]
[TD="align: right"]48[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]32:22[/TD]
[TD]Planned[/TD]
[TD]Blank (For Now)[/TD]
[TD]Yes[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]14:24[/TD]
[TD]Unplanned[/TD]
[TD]Blank (For Now)[/TD]
[TD]Yes[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]18:21[/TD]
[TD]Unplanned[/TD]
[TD]Blank (For Now)[/TD]
[TD]No[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]