SUMPRODUCT in VBA

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

Code:

---

=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

Code:

---

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

---

Any help please ....

You need to use .Evaluate

Try this

Code:

---

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

---

Code:

---

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

---

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

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

Code:

---

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

---

Thanks a lot Mr. Paul for illustration ..

Quote:

---

Originally Posted by 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

---

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.

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