Countif using arrays

[YasserKhalil]

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]

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]

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]

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]

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]

Thank you very much for great help ..
Best Regards