Clever Geek Handbook

SUMPRODUCT Formula in VBA

I want to use the following formula SUMPRODUCT

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 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 this formula with its values ​​using vba?

+3


source to share


3 answers


Two possible simple solutions, given that the methods of the workheet function will not work on the arrays you are using:

Add the formula first and then replace it with the 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
+4


source


Guessing your use case:

  • C12

    is some kind of product that interests you.
  • Sale!$J$5:$J$1048576

    is a set of products
  • (Sale!$J$5:$J$1048576=C12)

    gives an array like {1,1,1,0,0,0...}

  • Sale!$D$5:$D$1048576

    is the unit price range.
  • Sale!$M$5:$M$1048576

    is the range for the number of units sold.
  • Yours SUMPRODUCT

    gives income for the product inC12

So, for this sample data:



enter image description here

You can use this code to use a lever 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
+2


source


I had the same problem with the sumproduct function and after a lot of experimentation I solved the 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
0


source






More articles:


All Articles