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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.