PDA

View Full Version : [SOLVED] Countif using arrays



YasserKhalil
08-27-2017, 01:12 PM
Hello everyone
I have devised this code that countifs using arrays ..


Sub COUNTIF_Using_Arrays()
Dim ws As Worksheet
Dim a As Variant
Dim b As Variant
Dim i As Long


Set ws = ThisWorkbook.Worksheets("Sheet1")
a = ws.Range("C3:IP2003").Value
b = ws.Range("IS3:IT2003").Value


For i = 1 To UBound(b, 1)
If b(i, 1) <> "" And Not IsEmpty(b(i, 1)) Then b(i, 2) = CountInArray(a, b(i, 1))
Next i

Application.ScreenUpdating = False
Application.Calculation = xlManual
ws.Range("IS3").Resize(UBound(b, 1), UBound(b, 2)).Value = b
Application.Calculation = xlAutomatic
Application.ScreenUpdating = True
End Sub


Function CountInArray(ByVal arr As Variant, ByVal vMatch As Variant)
Dim v As Variant

For Each v In arr
If v = vMatch Then CountInArray = CountInArray + 1
Next v
End Function


But it took some time although I used arrays ..
Of course using COUNTIFS built-in function make the file very slow so I am searching for alternative solution ..
Any ideas how to make it faster please

Thanks advanced for any help

Bob Phillips
08-27-2017, 02:52 PM
Sub COUNTIF_Using_Arrays()
Dim ws As Worksheet
Dim a As Range
Dim b As Range
Dim numrows As Long

Set ws = ThisWorkbook.Worksheets("Sheet1")
Set a = ws.Range("C3:IP2003")
Set b = ws.Range("IP3:IY2003")

numrows = Application.Evaluate("MAX(ROW(" & b.Address & "))") - Application.Evaluate("MIN(ROW(" & b.Address & "))") + 1
With b.Cells(1, 2).Resize(numrows)

.FormulaR1C1 = "=IF(RC[-1]="""","""",COUNTIF(" & a.Address(, , xlR1C1) & ",RC[-1]))"
.Value = .Value
End With

Application.ScreenUpdating = True
End Sub

YasserKhalil
08-27-2017, 04:25 PM
Thanks a lot my friend
But in fact I tried using COUNTIF in the code itself as you did now but I encountered that the code is somewhat slow.. I am confused about what to do?

OK I will use your code, it is faster a little than mine in fact ...
How can I count the adjacent numbers to the found ..
For example if the criteria was "book" and this criteria was in column C .. I need to count the number in the adjacent cell
If the criteria "book" was in C5 for example .. I want to deal with the number in D5 .. I mean not to count the criteria itself but to sum the number in next column

Aflatoon
08-27-2017, 11:50 PM
I would think that it would be faster to loop through the a range once collating all the data into a dictionary and then just loop through the b range retrieving the counts for each item, rather than looping through the whole a range every time.

Bob Phillips
08-29-2017, 02:51 AM
How can I count the adjacent numbers to the found ..
For example if the criteria was "book" and this criteria was in column C .. I need to count the number in the adjacent cell
If the criteria "book" was in C5 for example .. I want to deal with the number in D5 .. I mean not to count the criteria itself but to sum the number in next column

Use SUMIF on column D rather than COUNTIF.

YasserKhalil
08-29-2017, 04:14 AM
Thank you very much for great help ..
Best Regards