title: Array CountIf Substitute - Count(Match())
body: <h2>Core Question</h2>
  
  <p>How can I perform repeated <code>CountIf()</code>s on a range as efficiently (performance-wise) as possible?</p>
  
  <p><br></p>
  
  <h2>Concerns in Detail</h2>
  
  <p><strong>Range to Array</strong></p>
  
  <p>Since each read/write to a spreadsheet results in slower VBA code, it is advisable to do so as few times as possible. Normally, if someone is repeatedly reading/writing to a range, he or she should first save that range to an array, perform the operations to the array, and then do a final read or write to the spreadsheet if necessary.</p>
  
  <p><br>
  <strong>Example Values and Code</strong></p>
  
  <p><a href="https://i.stack.imgur.com/owyrv.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/owyrv.png" alt="Example Values"></a></p>
  
  <p>How can I use perform <code>CountIf()</code>s on the range of <code>A2:A11</code> above to calculate the count of each value and write them to <code>D2:D7</code>? I would expect the below code to work:</p>
  
  <pre><code>Sub M1ArrayCount()
  
  Dim arrNumbers() As Variant
  Dim Long1 As Long
  Dim Loop1 As Long
  
  arrNumbers() = ThisWorkbook.Sheets(1).Range("A2:A11").Value
  
  With ThisWorkbook.Sheets(1)
      For Loop1 = 1 To 6
          .Cells(Loop1 + 1, 4).Value = Application.CountIf(arrNumbers(), Loop1)
      Next Loop1
  End With
  
  End Sub
  </code></pre>
  
  <p><strong>CountIf() Doesn't Work with Arrays</strong></p>
  
  <p>However, because <code>Application.CountIf()</code> only works with ranges and not arrays, <code>D2:D7</code> all show <code>#VALUE!</code> errors after running the above code. A substitute must be found.</p>