?
Solved

SUMPRODUCT not working

Posted on 2010-01-07
10
Medium Priority
?
663 Views
Last Modified: 2012-06-27
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

0
Comment
Question by:bromy2004
  • 3
  • 3
  • 3
  • +1
10 Comments
 
LVL 81

Accepted Solution

by:
zorvek (Kevin Jones) earned 1800 total points
ID: 26198251
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
 
LVL 8

Expert Comment

by:dandraka
ID: 26198530
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
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 26198564
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 10

Author Comment

by:bromy2004
ID: 26206233
Is there a suitable VBA code sample that i could substitute instead of the SUMPRODUCT?
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 26207909
There's one at the end of Kevin's post?
0
 
LVL 10

Author Comment

by:bromy2004
ID: 26207917
Without Using Evaluate.
with VBA Syntax
0
 
LVL 85

Assisted Solution

by:Rory Archibald
Rory Archibald earned 200 total points
ID: 26207963
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
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 26208054
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
 
LVL 10

Author Comment

by:bromy2004
ID: 26208066
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
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 26208074
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

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question