Excel SUMPRODUCT Function
The Excel SUMPRODUCT function multiplies corresponding ranges and returns the sum of these values
Example: Excel SUMPRODUCT Function
=SUMPRODUCT(C5:C11,D5:D11)
|
Result in cell F5 (13,980) - multiplies the values in range (C5:C11) by values in range (D5:D11).
|
METHOD 2. Excel SUMPRODUCT function using the Excel built-in function library
EXCEL
=SUMPRODUCT(C5:C11,D5:D11) Note: in this example we are multiplying the numbers in range (C5:C11) by numbers in range (D5:D11). |
Dim ws As Worksheet
ws.Range("F5") = Application.WorksheetFunction.SumProduct(ws.Range("C5:C11"), ws.Range("D5:D11"))
End Sub
Worksheets: The Worksheets object represents all of the worksheets in a workbook, excluding chart sheets.
Range: The Range object is a representation of a single cell or a range of cells in a worksheet.
Worksheet Name: Have a worksheet named SUMPRODUCT.
Array: Ensure that the data you want to multiply by is captured in ranges ("C5:C11") and ("D5:D11") in the worksheet.
ADJUSTABLE PARAMETERS
Output Range: Select the output range by changing the cell reference ("F5") in the VBA code to any cell in the worksheet, that doesn't conflict with formula.
Array: Select the ranges that you want to multiply by changing ranges ("C5:C11") and ("D5:D11") to any ranges in the worksheet, that doesn't conflict with the formula
The Excel SUMPRODUCT function multiplies corresponding ranges and returns the sum of these values.
=SUMPRODUCT(array1, [array2], ...)
array1: (Required) The first range of cells that you want to multiply.
array2: (Optional) The second range of cells that you want to multiply.
ADDITIONAL NOTES
Note 1: In Excel 2007 and later the SUMPRODUCT function can accept up to 255 value arguments. In Excel 2003 the SUMPRODUCT function can only accept up to 30 value arguments.
Note 2: All arrays / ranges in the SUMPRODUCT function must have the same amount of rows and columns.
Note 3: The non-numeric values in the selected ranges will be treated as 0s.
Note 3: The Excel SUMPRODUCT function does not support wildcards.