Solved

# SUMPRODUCT not working

Posted on 2010-01-07
631 Views
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)))
0
Question by:bromy2004

LVL 81

Accepted Solution

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

Zorvek is correct...and you can also read up on this:

Good luck
0

LVL 85

Expert Comment

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

LVL 10

Author Comment

Is there a suitable VBA code sample that i could substitute instead of the SUMPRODUCT?
0

LVL 85

Expert Comment

There's one at the end of Kevin's post?
0

LVL 10

Author Comment

Without Using Evaluate.
with VBA Syntax
0

LVL 85

Assisted Solution

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

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

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)

0

LVL 81

Expert Comment

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

### Suggested Solutions

A2 = A1 That kind of cell reference is relative.  If you copy it from A2 to B2, then B2 will get this: B2 = B1 That's all fine and good, but if you then insert a new row above row 2, you'll find: A3 = A1 B3 = B1 This is intentional. …
A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.