CODEWITHSUNDEEP
excelvba
Shubham Korpe
Shubham Korpe

Reputation: 13

Using Sum with CountIfs using VBA

I am trying to use SUM with CountIfs.

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 I use it with VBA?

I 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 get this error:

Unable to get countif property of the worksheetfunction class

Upvotes: 1

Views: 440

Answers (1)

Rory
Rory

Reputation: 34045

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")))

Upvotes: 3

Related Questions