SumProduct

[aftabn10]

I am looking to write an Excel SumProduct but within a database and was wondering if somebody help guide me to any tutorials or help me to understand the basics of converting one to a database query.

Thanks in advance.

 

[MarkK]

What is an Excel SumProduct? People around here know a lot more about Access than Excel.

 

[SOS]

What is an Excel SumProduct? People around here know a lot more about Access than Excel.

From the Excel Help File:

Multiplies corresponding components in the given arrays, and returns the sum of those products.
Syntax
SUMPRODUCT(array1,array2,array3, ...)
Array1, array2, array3, ... are 2 to 30 arrays whose components you want to multiply and then add.
Remarks

• The array arguments must have the same dimensions. If they do not, SUMPRODUCT returns the #VALUE! error value.
• SUMPRODUCT treats array entries that are not numeric as if they were zeros.

 

[MarkK]

This seems to work, but it doesn't require that all arrays be the same size ...

Sub TestExcelSumProduct()
   Dim vArray(2)
   vArray(0) = Array(1, 2, 3)
   vArray(1) = Array(2, 3, "4")
   vArray(2) = Array(3, 4, "This is not a number")
   
   Dim var As Variant
   var = SumProduct(vArray(0), vArray(1), vArray(2))
   Debug.Print var, TypeName(var)
End Sub

Function SumProduct(ParamArray Arrays() As Variant)
   Dim vArray As Variant
   Dim vElement As Variant
   Dim product As Variant
   Dim sum As Variant
   
[COLOR="Green"]   'traverse arrays[/COLOR]
   For Each vArray In Arrays()
      product = 1
[COLOR="Green"]      'traverse array elements[/COLOR]
      For Each vElement In vArray
         If IsNumeric(vElement) Then
[COLOR="Green"]            'multiply numeric array members[/COLOR]
            product = product * vElement
         Else
[COLOR="Green"]            'treat non-numeric members as zeros[/COLOR]
            product = 0
            Exit For
         End If
      Next
      sum = sum + product
   Next
   SumProduct = sum
   
End Function

Or you can automate Excel ...

Sub UseExcelWorksheetFunction()
   Dim vArray(2)
   vArray(0) = Array(1, 2, 3)
   vArray(1) = Array(2, 3, "4")
   vArray(2) = Array(3, 4, "This is not a number")
   
   Dim app As Object
   Set app = CreateObject("Excel.Application")
   
   Dim var
   var = app.worksheetfunction.SumProduct(vArray(0), vArray(1), vArray(2))
   app.Quit
   Set app = Nothing
   
   Debug.Print var, TypeName(var)

End Sub