[SOLVED] Countifs Property Run-time error with String Criteria

Issue

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.

Solution

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)

Answered By – Drawleeh

Answer Checked By – Marilyn (BugsFixing Volunteer)