title: COUNTIF formula - Counting number of occurrences a particular time occurs between two time intervals
body: <p>I am trying to use a COUNTIF forumla to determine:</p>
  
  <ol>
  <li>If a particular time (<strong>Sheet 1 Col B</strong> as pictured) occurs between any of the time intervals in <strong>Sheet 2 ColF&amp;G</strong></li>
  <li>Do a count of how many instances that fall within each time range (<strong>Sheet 2 ColF&amp;G</strong>) reading from <strong>Sheet 1 Col B</strong></li>
  </ol>
  
  <p>The goal is that there is a target value count for each time interval (<strong>Sheet 2 Col H</strong>) and I want to see if the target is met each time or not through conditional formatting.</p>
  
  <p>Sheet 1: </p>
  
  <p><a href="https://i.stack.imgur.com/J1NSb.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/J1NSb.png" alt="enter image description here"></a></p>
  
  <p>Sheet 2:</p>
  
  <p><a href="https://i.stack.imgur.com/IMKv6.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/IMKv6.png" alt="enter image description here"></a></p>
  
  <p>I have written a similar piece of VBA code to determine if the time falls within the time intervals, but goal is to automatically update with a formula rather than running a macro for it. Any help would be appreciated. </p>
  
  <p><strong>VBA code (using an array) as below incase anyone finds it useful:</strong></p>
  
  <pre><code>Dim Start As Variant, fin As Variant,Test As Variant, xa As Long, y1 As Long, Z1 As Long, k1 As Long: k1 = 2
  
  Start = ThisWorkbook.Sheets(2).Range("A2:A" &amp; ThisWorkbook.Sheets(2).Cells(ThisWorkbook.Sheets(2).Rows.Count, 1).End(xlUp).Row).Value2
  fin = ThisWorkbook.Sheets(2).Range("B2:C" &amp; ThisWorkbook.Sheets(2).Cells(ThisWorkbook.Sheets(2).Rows.Count, 1).End(xlUp).Row).Value2
  Test = ThisWorkbook.Sheets(4).Range("J2:J" &amp; ThisWorkbook.Sheets(4).Cells(ThisWorkbook.Sheets(4).Rows.Count, 1).End(xlUp).Row).Value2
  
         isFound = False
          For xa = LBound(Test) To UBound(Test)
              For y1 = LBound(Start) To UBound(Start)
                  For Z1 = LBound(fin) To UBound(fin)
  
              If Test(xa, 1) &gt;= Start(y1, 1) And Test(xa, 1) &lt;= fin(Z1, 1) Then
  
              ThisWorkbook.Sheets(4).Cells(k1, 12) = fin(Z1, 2)
  
                  k1 = k1 + 1
  
              isFound = True
  
              End If
                  y1 = y1 + 1
  
              Next
          Next
          If Not isFound Then
  
          End If
  
      Next
  </code></pre>