Using Sum with Countifs Vba-CodePudding

I am new to vba and have been trying to use SUM with Countifs for a while. Unfortunately I am unable to get rid of the errors.

I have two worksheets "SumWorkPlace" and "Astarentries". In column A of "SumWorkPlace" there are over 25k code numbers. I want to get sum in Column C of "SumWorkPlace" for each code with following criterias: 1.) If the code is present in Range("A:A") of "Astarentries". 2.) It should also satisfy : ("001,008,009,010,012,L01,L02,L03,L04,L05,L06") these values in Range("C:C") of "Astarentries".

I am able to do it with this formula:

=SUM(COUNTIFS('Astarentries'!A:A,'SumWorkPlace'!A2,'Astarentries'!C:C,{"001","008","009","010","012","L01","L02","L03","L04","L05","L06"}))

How can use it with vba?

Here is something that I have tried:

Sub Calculate()

Dim ws1 As Worksheet
Dim ws2 As Worksheet

Dim L1 As Integer

Set ws1 = Worksheets("SumWorkPlace")
Set ws2 = Worksheets("Astarentries")

With ws1
L1 = .Cells(Rows.Count, 1).End(xlUp).Row

For i = 2 To L1

ws1.Cells(i, 3).Value = Application.WorksheetFunction.Sum(Application.WorksheetFunction.CountIfs(ws2.Range("A:A"), ws1.Cells(i, 1), ws2.Range("C:C"), "001", "008", "009", "010", "012", "L01", "L02", "L03", "L04", "L05", "L06"))

Next i

End With

End Sub

I am getting this error:

Unable to get countif property of the worksheetfunction class

Please guide me if there is any other way to do it.

I thank you in advance.

CodePudding user response：

You need an array for the criteria and you have to use application.countifs not worksheetfunction.countifs:

ws1.Cells(i, 3).Value = Application.WorksheetFunction.Sum(Application.CountIfs(ws2.Range("A:A"), ws1.Cells(i, 1), ws2.Range("C:C"), Array("001", "008", "009", "010", "012", "L01", "L02", "L03", "L04", "L05", "L06")))