CODEWITHSUNDEEP
arraysexcelvbacountif
user4691433
user4691433

Reputation:

Array CountIf Substitute - Count(Match())

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

Example Values

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.

Upvotes: 5

Views: 12558

Answers (2)

Guest
Guest

Reputation: 1

Dim rowin as integer 'this is a counter for the row index
For rowin = 2 To Sheets("Sheet1").UsedRange.Rows.Count
Sheets("Sheet1").Range("D" & rowin).Value = WorksheetFunction.CountIf(Range("A:A"), Range("C" & rowin))
Next

Upvotes: 0

user4691433
user4691433

Reputation:

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

Final Results


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

Upvotes: 10

Related Questions