Excel SUMPRODUCT Function

The Excel SUMPRODUCT function multiplies corresponding ranges and returns the sum of these values

Example: Excel SUMPRODUCT Function

METHOD 1. Excel SUMPRODUCT Function

EXCEL

=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

Formulas tab > Function Library group > Math & Trig > SUMPRODUCT > populate the input boxes

=SUMPRODUCT(C5:C11,D5:D11)
Note: in this example we are multiplying the numbers in range (C5:C11) by numbers in range (D5:D11).

METHOD 1. Excel SUMPRODUCT function using VBA

VBA

Sub Excel_SUMPRODUCT_Function()

'declare a variable
Dim ws As Worksheet

Set ws = Worksheets("SUMPRODUCT")

'apply the Excel SUMPRODUCT function
ws.Range("F5") = Application.WorksheetFunction.SumProduct(ws.Range("C5:C11"), ws.Range("D5:D11"))

End Sub

OBJECTS
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.

PREREQUISITES
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

Usage of the Excel SUMPRODUCT function and formula syntax

EXPLANATION

DESCRIPTION
The Excel SUMPRODUCT function multiplies corresponding ranges and returns the sum of these values.

SYNTAX
=SUMPRODUCT(array1, [array2], ...)

ARGUMENTS
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.