Array CountIf Substitute - Count(Match())
<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>