SUMPRODUCT Formula in VBA

Question

I want to use following SUMPRODUCT formula in VBA:

=SUMPRODUCT((Sale!$J$5:$J$1048576=C12)*Sale!$D$5:$D$1048576,Sale!$M$5:$M$1048576)

I used this code but it gives an error

Run-time error '13': Type mismatch

Dim slWks As Worksheet

Set slWks = Sheets("Sale")
ActiveSheet.Range("F12").Value = _
Application.WorksheetFunction.SumProduct((slWks.Range("J5:J1048576") = _
    ActiveSheet.Range("C12")) * slWks.Range("D5:D1048576"), slWks.Range("M5:M1048576"))

How can I write that formula with its values using vba?

Answer 1

Taking a guess at your use case:

• C12 is some product you are interested in
• Sale!$J$5:$J$1048576 is a range of products
• (Sale!$J$5:$J$1048576=C12) gives an array like {1,1,1,0,0,0...}
• Sale!$D$5:$D$1048576 is a range of unit prices
• Sale!$M$5:$M$1048576 is a range of number of units sold
• Your SUMPRODUCT gives a the revenue of for the product in C12

So for this sample data:

You could use this code to do leverage SUMPRODUCT:

Option Explicit

Sub SumProductWithVBA()

    Dim ws As Worksheet
    Dim strProduct As String
    Dim dblRevenue As Double
    Dim wsf As WorksheetFunction
    Dim v1 As Variant, v2 As Variant, v3 As Variant, v4 As Variant
    Dim i As Long

    Set wsf = Application.WorksheetFunction
    Set ws = ThisWorkbook.Worksheets("Sheet1")

    ' condition for SUMPRODUCT
    strProduct = ws.Range("A1").Value

    ' get the values of the ranges
    v1 = wsf.Transpose(ws.Range("A4:A15").Value)
    v2 = wsf.Transpose(ws.Range("B4:B15").Value)
    v3 = wsf.Transpose(ws.Range("C4:C15").Value)

    ' make the array like {1,1,1,0,0,0...etc}
    ' this is the equivalent of the SUMPRODUCT((range=value)... bit
    ReDim v4(1 To UBound(v1))
    For i = 1 To UBound(v1)
        If v1(i) = strProduct Then
            v4(i) = 1
        Else
            v4(i) = 0
        End If
    Next i

    ' now do the SUMPRODUCT with all the arrays set-up    
    dblRevenue = wsf.SumProduct(v4, v2, v3)

    ' test the output    
    MsgBox dblRevenue

End Sub

Answer 2

I had the same problem with sumproduct function and after many experiments I solved my problem with this code:

Sub Test2()
  Dim WS As Worksheet
  Dim a, b, c, Criteria As Range
  Dim data1, data2, data3, crite As String
  Dim LasTRow As Long

  Set WS = ThisWorkbook.Sheets("Sale")
  LasTRow = WS.Cells(Rows.Count, 1).End(xlUp).Row

  Set a = WS.Range("A5:A" & LasTRow)
  Set b = WS.Range("b5:b" & LasTRow)
  Set c = WS.Range("c5:c" & LasTRow)
  Set Criteria = WS.Range("A1")

  data1 = a.Address
  data2 = b.Address
  data3 = c.Address
  crite = Criteria.Address
  WS.Range("b1").Formula = "=sumproduct((" & data1 & " = " & crite & ") *( " & data2 & " ) * ( " & data3 & " ))"

End Sub

Answer 3

Two possible simple solutions, given that worksheetfunction methods won't work with arrays the size that you are using:

First, add the formula and then replace it with its value

With activesheet.Range("F12")
   .Formula =" =SUMPRODUCT((Sale!$J$5:$J$1048576=C12)*Sale!$D$5:$D$1048576,Sale!$M$5:$M$1048576)"
   .Value2 = .Value2
End With

Second, use Evaluate:

With Activesheet
   .range("F12").Value2 = .Evaluate("SUMPRODUCT((Sale!$J$5:$J$1048576=C12)*Sale!$D$5:$D$1048576,Sale!$M$5:$M$1048576)")
End With