CODEWITHSUNDEEP
vbaexcel
CaptainABC
CaptainABC

Reputation: 1239

Application.Worksheetfunction Sumproduct issue

I have an excel sheet and I am trying to use a countifs formula to count the number or records meeting multiple conditions, however in one of the columns there are multiple criterias so I used SUMPRODUCT together with the COUNTIFS function.

I got it to work fine in the sheet but I have no idea on how to get it to work in VBA.

This is what I tried:

Dim lastrow As Long
lastrow = Sheet2.Cells(Sheet2.Rows.Count, "M").End(xlUp).Row
FirstDate = Sheets("Sheet1").Range("C7")
SecondDate = Sheets("Sheet1").Range("E7")

   Application.Worksheetfunction.Sumproduct(CountIfs(Sheet2.Range("E2:E" & lastrow), ">=" & FirstDate, Sheet2.Range("E2:E" & lastrow), "<=" & SecondDate, Sheet2.Range("P2:P" & lastrow), {"John";"James";"Peter"}))

I keep getting an error when using the above formula. Can you please tell me what I'm doing wrong.

Thanks in advance.

EDIT: This is the formula I'm trying to mimic:

=Sumproduct(CountIfs(Sheet2!E2:E1000000,">="&Sheet1!C7,Sheet2!E2:E1000000,"<="&Sheet1!E7,Sheet2!E2:E1000000,{"John";"James";"Peter"}))

I don't want VBA to insert this formula into the cell, I'd rather have it calculate the value and then insert the result into the chosen cell.

Upvotes: 1

Views: 7509

Answers (1)

Tim Williams
Tim Williams

Reputation: 166316

Seems to work for me:

Sub Tester()

    Dim v, wsf
    Dim lastrow As Long

    lastrow = Sheet2.Cells(Sheet2.Rows.Count, "M").End(xlUp).Row

    Set wsf = Application.WorksheetFunction

    v = Application.WorksheetFunction.SumProduct(wsf.CountIfs( _
        Sheet2.Range("E1:E" & lastrow), ">=" & Sheet1.Range("C7").Value, _
        Sheet2.Range("E1:E" & lastrow), "<=" & Sheet1.Range("E7").Value, _
        Sheet2.Range("P1:P" & lastrow), Array("John", "James", "Peter")))

    Debug.Print v

End Sub

Upvotes: 2

Related Questions