# Trying to write a SumProduct formula (using double negative method ) in VBA

I'm trying to recreate this formula in VBA and getting a "Type mismatch" error. Able to do it via R1C1 and then .filldown, but explicitly trying to avoid that has it's super slow in the real data set.

Can't figure out what I'm doing wrong. I've used this method in simpler SumIf formulas before...

``````Sub WriteFormulas()

Dim rQnty As Range, rName As Range, rDate As Range
Dim vQnty As Variant, vName As Variant, vDate As Variant
Dim j As Double, dValue As Double

With ThisWorkbook.Worksheets(1)
Set rQnty = .Range("A2:A10")
Set rName = .Range("B2:B10")
Set rDate = .Range("C2:C10")
End With

vQnty = rQnty.Value
vName = rName.Value
vDate = rDate.Value

For j = 1 To UBound(vQnty)
dValue = WorksheetFunction.SumProduct((rQnty), --(rName = vName(j, 1)), --(rDate = vDate(j, 1)))
Next j

'Range("D2").FormulaR1C1 = "=SUMPRODUCT(RC[-3]:R[8]C[-3],--(RC[-2]:R[8]C[-2]=""John""),--(RC[-1]:R[8]C[-1]=""Jan""))"

End Sub``````
Capture.PNG
byundt

SUMPRODUCT does not work well in VBA. As flexible as it is in a worksheet formula, it must be handled with kid gloves in VBA.
• VBA will not accept multiplying one parameter by another. You must separate each parameter with commas.
• VBA will not accept Boolean expressions as one of the parameters. But you are able to use the Evaluate method to convert the Boolean expression into something that SUMPRODUCT will accept.

I wrote up the results of some extensive testing in this old EE thread:
Norie

membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.