Countifs Property Run-time error with String Criteria

Question

I have not expressed worksheetfunction.countifs correctly.

I get:

Run-time error '1004':
Unable to get the CountIfs property of the WorksheetFunction Class

Sub test()
Dim Category(7 To 10) As Variant
Dim Ar As Variant
Dim Da As Range
Dim Cat As Range
Dim Br As Variant

Set Da = Range("A3:A" & Range("A" & Rows.Count).End(xlUp).Row)
Set Cat = Range("C3:C" & Range("C" & Rows.Count).End(xlUp).Row)

Ar = Range("F2").Value2
Br = Application.WorksheetFunction.EoMonth(Range("F2"), 0)

For i = 7 To 10
    Category(i) = Worksheets("Log").Cells(1, i).Value
    Cells(2, i) = Application.WorksheetFunction.CountIfs(Da, ">=" & Ar, Da, "<=" & Br, Cat, Category(i))
Next i

End Sub

It seems like the issue is with the last range and criteria on the sumifs function (the Cat,Category(i) where Category(i) is equal to a string).

If I split the sumif and use the Cat,Category on its own in a SUMIF, it works fine, and the other two criteria's work fine alone.

Answer 1

So it was actually due to the way that the string criteria was being declared, changing that fixed it.

Cells(2, i) = Application.WorksheetFunction.CountIfs(Range("C3", Range("C3").End(xlDown)), "=" & test & "*", Range("A3", Range("A3").End(xlDown)), ">=" & Ar, Range("A3", Range("A3").End(xlDown)), "<=" & Br)