title: VBA - Countifs time and date by building using variants
body: <p>I received help in solving a previous <a href="https://stackoverflow.com/questions/47601437/vba-countif-cell-range-displayed-value-is-equal-to-desired-value">question</a>.  I would like to solve this problem similarly.</p>
  
  <p>So the situation is similar to a <code>Countifs</code> function, in that I would like it to count if a range equals a certain building, as well as if the date and time that is offset equals a certain date.  For example, if the cell in <code>"C1" = "Irving Building"</code> And if the value in <code>"K1" = "Monday"</code> Then I would like it to display in <code>"S1"</code>.  More specifically if <code>"C1" = "Irving Building"</code> then I want it to count into whatever day and time that corresponds with it, in <code>Column K</code>.</p>
  
  <pre><code>Private Sub TimeAndDate()
  
  Dim n           As Double
  Dim rep         As Worksheet
  Dim ws          As Worksheet
  Dim LastRow     As Double
  
  Set rep = Worksheets("Report")
  
  rep.Columns("K:L").ClearContents
  
  For n = 1 To ThisWorkbook.Sheets.Count
  
      Set ws = Worksheets(n)
  
      If IsNumeric(ws.Name) Then
          LastRow = rep.Range("K1", rep.Range("K1").End(xlDown)).Rows.Count
          LastRow = LastRow + 1
  
          If rep.Range("K1") = "" Then
              ws.Range("C2", ws.Range("C2").End(xlDown)).Copy _
              Destination:=rep.Range("K1")
              ws.Range("C2", ws.Range("C2").End(xlDown)).Copy _
              Destination:=rep.Range("L1")
          Else:
              ws.Range("C2", ws.Range("C2").End(xlDown)).Copy _
              Destination:=rep.Range("K" &amp; LastRow)
              ws.Range("C2", ws.Range("C2").End(xlDown)).Copy _
              Destination:=rep.Range("L" &amp; LastRow)
          End If
  
      End If
  
  Next n
  
      Dim rDts As Range
      Dim vDts As Variant
      Dim vCnts As Variant
      Dim vAP As Variant    'for the AM PM count
      Dim vDbld As Variant  'for the date by building
      Dim vTbld As Variant  'for thee time by building
      Dim i As Long, J As Long
  
  'read dates into array -- faster processing
  With rep
      vDts = .Range(.Cells(1, 11), .Cells(.Rows.Count, 11).End(xlUp))
  End With
  
  'Results array
  ReDim vCnts(1 To 7, 1 To 2)
      vCnts(1, 1) = "Sunday"
      vCnts(2, 1) = "Monday"
      vCnts(3, 1) = "Tuesday"
      vCnts(4, 1) = "Wednesday"
      vCnts(5, 1) = "Thursday"
      vCnts(6, 1) = "Friday"
      vCnts(7, 1) = "Saturday"
  
  ReDim vAP(1 To 2, 1 To 2)
      vAP(1, 1) = "AM"
      vAP(2, 1) = "PM"
  
  ReDim vDbld(1 To 13, 1 To 2)
      vDbld(1, 1) = "Irving Building"
      vDbld(2, 1) = "Memorial Building"
      vDbld(3, 1) = "West Tower"
      vDbld(4, 1) = "Witting Surgical Center"
      vDbld(5, 1) = "Madison Irving Surgical Center"
      vDbld(6, 1) = "Marley Education Center"
      vDbld(7, 1) = "410 South Crouse"
      vDbld(8, 1) = "Physicians Office Building"
      vDbld(9, 1) = "Crouse Business Center"
      vDbld(10, 1) = "Commonwealth Place"
      vDbld(11, 1) = "Irving - Memorial Connector"
      vDbld(12, 1) = "Crouse Garage"
      vDbld(13, 1) = "CNY Medical Center"
  
  'Do the counts
      For i = 1 To UBound(vDts, 1)
  
          J = Weekday(vDts(i, 1))
          vCnts(J, 2) = vCnts(J, 2) + 1
  
          If Hour(vDts(i, 1)) &lt; 12 Then
              vAP(1, 2) = vAP(1, 2) + 1
          Else
              vAP(2, 2) = vAP(2, 2) + 1
          End If
  
  
      Next i
  
  'output the results
  rep.Range("E1:E14").Copy rep.Range("Q1")
  rep.Range("N2:N8").Copy
  rep.Range("R1").PasteSpecial xlPasteAll, xlPasteSpecialOperationNone, _
  False, True
  rep.Range("N11:N12").Copy
  rep.Range("Y1").PasteSpecial xlPasteAll, xlPasteSpecialOperationNone, _
  False, True
  
  rep.Range("N1") = "DATE"
  rep.Range("O1") = "COUNT"
  rep.Range("N10") = "TIME"
  rep.Range("O10") = "COUNT"
  rep.Range("N2:O8").Value = vCnts
  rep.Range("N11:O12").Value = vAP
  </code></pre>
  
  <p>The part that I am needing help on is this part here.  These are the variants that I'd like to use, but like I said, earlier, I am unsue as to how to do this without running a ton of <code>countifs</code> statements.</p>
  
  <pre><code>    Dim vDbld As Variant  'for the date by building
  ReDim vDbld(1 To 13, 1 To 2)
      vDbld(1, 1) = "Irving Building"
      vDbld(2, 1) = "Memorial Building"
      vDbld(3, 1) = "West Tower"
      vDbld(4, 1) = "Witting Surgical Center"
      vDbld(5, 1) = "Madison Irving Surgical Center"
      vDbld(6, 1) = "Marley Education Center"
      vDbld(7, 1) = "410 South Crouse"
      vDbld(8, 1) = "Physicians Office Building"
      vDbld(9, 1) = "Crouse Business Center"
      vDbld(10, 1) = "Commonwealth Place"
      vDbld(11, 1) = "Irving - Memorial Connector"
      vDbld(12, 1) = "Crouse Garage"
      vDbld(13, 1) = "CNY Medical Center"
  </code></pre>
  
  <p>I apologize if this is confusing, I am not completely sure how to word it, thanks in advance.</p>
  
  <p>This is an example of what I'd like it to look like:
  <a href="https://i.stack.imgur.com/oEasp.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/oEasp.png" alt="enter image description here"></a></p>