|
Classic file types are no longer available for use as of January 2021. You can transition your classic files or download a PDF. Learn More

Workiva Support Center

SUMPRODUCT

SUMPRODUCT

  • Last updated on

Description 

Use this function to multiply ranges together and return the sum of the products. Supported in Chains. 

 

Syntax

SUMPRODUCT(range_1, […, range_254])

 

Inputs

This function accepts the following arguments:

Name Required Description Valid input
range_1 Yes The first range of cells to be multiplied and then totaled. If no other range is provided, then the values are multiplied by 1. A cell, a cell range, or a formula which results in a cell range.  
range_n No The subsequent range(s) of cells to be multiplied and then totaled. Up to 254 additional ranges can be included. A cell reference (including a range), a number, or a formula which results in either of these.  

 

Examples

Sample data

A B C D E
1 Date Color State Quantity Value
2 2-Jan Red TX 4 $23.75
3 18-Feb Blue OH 3 $27.80
4 28-Feb Blue NJ 2 $31.20
5 15-Mar Green TX 3 $45.23
6 13-Apr Red CO 4 $58.60
7 30-Apr Red IL 2 $55.10
8 20-May Red AZ 1 $48.90
9 14-Jun Green WA 2 $52.30
10 22-Jul Red NY 4 $62.75
11 11-Aug Green TX 4 $41.65
12 19-Aug Green OR 1 $43.70
13 3-Sep Green FL 1 $33.50
14 6-Oct Green MA 3 $39.15
15 25-Oct Blue GA 1 $29.99
16 7-Nov Blue WA 2 $18.99
17 9-Dec Red TX 3 $37.42

Sample formulas

Use case Formula Explanation and Result
Calculate the sum of the products of corresponding numbers in two ranges. =SUMPRODUCT(D2:D16, E2:E16)

This formula works in the following manner:

  • SUMPRODUCT(: This is the function itself.
  • D2:D16: The first array (Quantity).
  • E2:E16: The second array (Cost).
  • The function multiplies the corresponding values from each array (D2*E2, D3*E3, etc.) and then sums those products.

Result: 1604.81.
Get the sum of a single range of numbers. =SUMPRODUCT(D2:D16)

This formula works in the following manner:

  • SUMPRODUCT(: This is the function itself.
  • D2:D16: When given a single array, SUMPRODUCT behaves just like the SUM function.
  • The formula sums all values in the range D2:D16.

Result: 37.

 

Notes

  • SUMPRODUCT supports up to 255 ranges.
  • SUMPRODUCT treats non-numeric items (including text, errors, and logical values) as zeros.
  • All ranges must be the same size or SUMPRODUCT will return a #VALUE! error.

 

Tips

  • For best performance, SUMPRODUCT should not be used with full column (A:A) references.

 

Related functions

Description Use this function to multiply ranges together and return the sum of the products. Supported in Chains.  S...