Array CountIf Substitute - Count(Match())

Question

Core Question

How can I perform repeated CountIf()s on a range as efficiently (performance-wise) as possible?

Concerns in Detail

Range to Array

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.

Example Values and Code

How can I use perform CountIf()s on the range of A2:A11 above to calculate the count of each value and write them to D2:D7? I would expect the below code to work:

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

CountIf() Doesn't Work with Arrays

However, because Application.CountIf() only works with ranges and not arrays, D2:D7 all show #VALUE! errors after running the above code. A substitute must be found.

Answer 1

Solution - Count(Match())

The solution we are looking for is:

Application.Count(Application.Match(SavedArray(), Array([lookup_value]), 0))

What? How does that work? --- How can a function that normally returns a row number be paired with an array and count to return the correct answer? How can you count row numbers?

Explanation of Mechanics

With many thanks to @Jeeped, here's how it works:

It's an undocumented feature of Match(lookup value, lookup array, 0) that if you put an array as the lookup value, it will Match() each value in the array you entered against the lookup array. Thus, for the above example, for Loop = 1, it will become:

{match(A2, Array("1"), 0),match(A3, Array("1"), 0), ... match(A11, Array("1"), 0)}

Then, per @Jeeped:

Each match will either return a number or an error. The Count() function counts numbers, not errors. So you get a count of whether any of the values in A1:A11 match Loop1.

Final Code and Values

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.Count(Application.Match(arrNumbers(), Array(Loop1), 0))
    Next Loop1
End With

End Sub

References --- This comment

This answer

Question

Example:

myarray = array("First","Second","Second","Third","Fourth")

then what would the countif(myarray,"second") syntax be? (the result should equal 2 counts)

Answer

Try also:

MsgBox Application.Count(Application.Match(myArray, Array("Second"), 0))

This chat