Search code examples
excelvbasumproductworksheet-function

Sumproduct as a function in VBA

I would like to see if it is possible to make a sumproduct function in VBA. I can make one that works perfectly fine in Excel that looks like this:

=SUMPRODUCT(C2:C19,((D2:D19="text1")*12+(D2:D19="text2")*4+(D2:D19="text3")*2))

I have 2 columns, and the goal is to multiply a cell from the first column with a number, based on different text from a cell from the other column. This is done as shown above. However, as I use it quite a bit, it actually takes some time to write everytime, so I wanted to see if I could make a function in VBA that could just select x (the first column) and y (the second column) and the result would be there. I have worked on this so far, but haven't been able to make it work:

Function xxx(x, y)
    xxx = Application.WorksheetFunction.SumProduct(x, ((y = "text1") * 12 + y = "text2" * 4 + y = "text3" * 2))
End Function

I can't make any specific references to rows and columns for example C2:C19, as it varies a lot, so I just want to select the first column (x) and the second column (y) as range. Any suggetions? Thanks for reading! :)

Solution

  • Try one of the following functions...

    Function xxx(x As Range, y As Range)
    xxx = Evaluate("SUMPRODUCT(" & x.Address & ",((" & y.Address & "=""text1"")*12+(" & y.Address & "=""text2"")*4+(" & y.Address & "=""text3"")*2))")
End Function

Sub Test1()
MsgBox xxx(Range("C2:C19"), Range("D2:D19"))
End Sub

    OR

    Function xxx(x As String, y As String)
    xxx = Evaluate("SUMPRODUCT(" & x & ",((" & y & "=""text1"")*12+(" & y & "=""text2"")*4+(" & y & "=""text3"")*2))")
End Function

Sub Test2()
MsgBox xxx("C2:C19", "D2:D19")
End Sub