SUMPRODUCT in VBA

[YasserKhalil]

Hello everyone
I am stuck of the use of the SUMPRODUCT in VBA
I have this formula

=SUMPRODUCT((SH!$D$12:$D$22="Male")*(SH!$E$12:$E$22=1))

And I used variables for ranges :
rGender = SH!$D$12:$D$22
rGrade =SH!$E$12:$E$22

When I tried this line it failed

MsgBox Application.WorksheetFunction.SumProduct(rGender, strMale, rGrade, grade)

Any help please ....

[Paul_Hossler]

You need to use .Evaluate

Try this

Option Explicit

'ref: http://www.xldynamic.com/source/xld.SUMPRODUCT.html

Sub demo()
    Dim rGender As Range, rGrade As Range
    Dim strMale As String, strFormula As String
    Dim iGrade As Long
    
    strMale = "Male"
    iGrade = 1
    Set rGender = Worksheets("SH").Range("$D$12:$D$22")
    Set rGrade = Worksheets("SH").Range("$E$12:$E$22")

    '=SUMPRODUCT(($D$12:$D$22="Male")*($E$12:$E$22=1))

    'note the extra quotes around strMale
    strFormula = "=SUMPRODUCT((" & rGender.Parent.Name & "!" & rGender.Address & "=""" & strMale & """)*(" & rGrade.Parent.Name & "!" & rGrade.Address & "=" & iGrade & "))"

    MsgBox strFormula
    MsgBox Application.Evaluate(strFormula)

End Sub

[mikerickson]

MsgBox WorksheetFunction.CountIfs(Worksheets("SH").Range("$D$12:$D$22"), "Male", Worksheets("SH").Range("E12:E22"),1 )

[YasserKhalil]

Thank you very much for these perfect solutions
I wonder why can't sumproduct used directly in VBA although it is built in worksheetfunction .. that's weird

[Paul_Hossler]

You can call it via VBA, but I don't think you can use it the way you wanted to

Option Explicit
Sub Macro1()
    Range("A1:A10").Value = 2
    Range("B1:B10").Value = 3
    Range("C1:C10").Value = 4
    Range("D1:D10").Value = 7
    Range("E1:E10").Value = 11
    
    MsgBox Application.WorksheetFunction.SumProduct(Range("A1:A10"), Range("B1:B10"), Range("C1:C10"), Range("D1:D10"), Range("E1:E10"))
End Sub

[YasserKhalil]

Thanks a lot Mr. Paul for illustration ..

[Bob Phillips]

Thank you very much for these perfect solutions
I wonder why can't sumproduct used directly in VBA although it is built in worksheetfunction .. that's weird

When you use SUMPRODUCT for conditional counting/summing, you are effectively using an array function even though you don't CSE it, and you cannot just execute an array function in VBA, you need to evaluate it, so you need to do the same with SUMPRODUCT.

[YasserKhalil]

Thanks a lot xld for your reply and for this useful information
Best Regards