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

LVL 10
bromy2004Asked:
Who is Participating?
 
zorvek (Kevin Jones)Connect With a Mentor ConsultantCommented:
The SUMPRODUCT function in a worksheet formula is a very powerfull tool for applying array formula conditional logic to problems. From VBA however, the SumProduct function (available via the Application or Application.WorksheetFunctions objects) is not as flexible and complex array constucts cannot be used. The reason is the parameter typing is much more strict from VBA and each parameter passed to SumProduct must be an array - it cannot be a string. While the following example formula:

   =SUMPRODUCT(A1:A10,B1:B10)

can be translated into a VBA form:

   MsgBox Application.SumProduct([A1:A10], [B1:B10])

This more complex version:

   =SUMPRODUCT((A1:A10=1)*B1:B10)

can not be translated.

To use the more complex forms of SUMPRODUCT syntax, use the Application.Evaluate function:

   MsgBox Application.Evaluate("SUMPRODUCT((A1:A10=1)*B1:B10)")

Kevin
0
 
dandrakaCommented:
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
0
 
Rory ArchibaldCommented:
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
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
bromy2004Author Commented:
Is there a suitable VBA code sample that i could substitute instead of the SUMPRODUCT?
0
 
Rory ArchibaldCommented:
There's one at the end of Kevin's post?
0
 
bromy2004Author Commented:
Without Using Evaluate.
with VBA Syntax
0
 
Rory ArchibaldConnect With a Mentor Commented:
It would just be a loop through the arrays checking each value and adding up the last array values if the criteria are met.
0
 
zorvek (Kevin Jones)ConsultantCommented:
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
0
 
bromy2004Author Commented:
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

0
 
zorvek (Kevin Jones)ConsultantCommented:
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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.