Link to home
Start Free TrialLog in
Avatar of bromy2004
bromy2004Flag for Australia

asked on

SUMPRODUCT not working

Why isn't this working?
popvalue = WorksheetFunction.SumProduct((PopSheet.Range("Values")) * (PopSheet.Range("RepNum") = PopRep) * (PopSheet.Range("BudgetName") = PopBudget) * (PopSheet.Range("Dates") = DateSerial(Year(PopDate), Month(PopDate), 1)))

PopDate=01/01/2010 as Date
PopRep=100 as String
PopBudget=DAY SALE as String
PopValue as Double
PopSheet as Worksheet

I keep getting a "Type Mismatch" error

All 4 Named Ranges are Valid.

Typing the Same function into Excel works.
Removing the Last 3 Sections is the only way i can get any sort of Value
popvalue = WorksheetFunction.SumProduct((PopSheet.Range("Values")) * (PopSheet.Range("RepNum") = PopRep) * (PopSheet.Range("BudgetName") = PopBudget) * (PopSheet.Range("Dates") = DateSerial(Year(PopDate), Month(PopDate), 1)))

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of zorvek (Kevin Jones)
zorvek (Kevin Jones)
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Zorvek is correct...and you can also read up on this:

(1) http://www.mrexcel.com/forum/showthread.php?t=65622
(2) http://www.ozgrid.com/forum/showthread.php?t=16044

Good luck
If you are going to use the Evaluate method, it is safer to use the evaluate method of the relevant worksheet, rather than the application, unless you have fully qualified all the range references in the formula with sheet names or you are sure that the right sheet is active.
FWIW
Rory
Avatar of bromy2004

ASKER

Is there a suitable VBA code sample that i could substitute instead of the SUMPRODUCT?
There's one at the end of Kevin's post?
Without Using Evaluate.
with VBA Syntax
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
This should do you well:

PopValue = Application.Evaluate("SUMPRODUCT(Values*(PopSheet!RepNum=" & PopRep & ")*(PopSheet!BudgetName=" & PopBudget & ")*(PopSheet!Dates=DateSerial(" & Year(PopDate) & "," & Month(PopDate) & ",1))")

Kevin
I ended up using

PopCommand = "SUMPRODUCT((" & PopSheet.Range("Values").Address & ") * (" & PopSheet.Range("RepNum").Address & "= " & Chr(34) & PopRep & Chr(34) & ") * (" & PopSheet.Range("BudgetName").Address & " = " & Chr(34) & PopBudget & Chr(34) & ") * (" & PopSheet.Range("Dates").Address & " = DATEVALUE(" & Chr(34) & DateSerial(Year(PopDate), Month(PopDate), 1) & Chr(34) & ")))"
            PopValue = PopSheet.Evaluate(PopCommand)

Open in new window

Oh dang, the quotes!

PopValue = Application.Evaluate("SUMPRODUCT(PopSheet!Values*(PopSheet!RepNum=""" & PopRep & """)*(PopSheet!BudgetName=" & PopBudget & ")*(PopSheet!Dates=DateSerial(" & Year(PopDate) & "," & Month(PopDate) & ",1))")

Kevin