excel - 使用 VBA 将 Sum 与 CountIfs 一起使用

Question

我正在尝试将 SUM 与 CountIfs 一起使用。
我有两个工作表“SumWorkPlace”和“Astarentries”。
在“SumWorkPlace”的 A 列中，有超过 25k 个代码编号。
我想根据以下条件在“SumWorkPlace”的 C 列中为每个代码获取总和:
1.) 如果代码存在于“Astarentries”的 Range(“A:A”) 中。
2.) 它还应该满足:("001,008,009,010,012,L01,L02,L03,L04,L05,L06") 这些值在 "Astarentries"的 Range("C:C") 中。
我可以用这个公式做到这一点:

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

如何将它与 VBA 一起使用？
我试过了:

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    

我收到此错误:

Unable to get countif property of the worksheetfunction class

Answer 1

您需要一个数组作为条件，并且必须使用 application.countifs不是 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")))