PDA

View Full Version : SUMPRODUCT in VBA



YasserKhalil
12-30-2016, 01:46 PM
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
12-30-2016, 03:50 PM
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
12-30-2016, 06:36 PM
MsgBox WorksheetFunction.CountIfs(Worksheets("SH").Range("$D$12:$D$22"), "Male", Worksheets("SH").Range("E12:E22"),1 )

YasserKhalil
12-30-2016, 11:05 PM
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
12-31-2016, 08:36 AM
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
12-31-2016, 09:14 AM
Thanks a lot Mr. Paul for illustration ..

Bob Phillips
01-04-2017, 02:57 AM
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
01-04-2017, 03:22 AM
Thanks a lot xld for your reply and for this useful information
Best Regards