SUMPRODUCT

• How to use SUMPRODUCT formula?
• Examples of using SUMPRODUCT formula
• SUMPRODUCT formula not working?
• Similar formulas to SUMPRODUCT

Usage

Use the SUMPRODUCT formula with the syntax shown below, it has 1 required parameter and 1 optional parameter:

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

1. array1 (required):
   The first array or range of cells to be multiplied. The arrays must have the same length.
2. [array2, ...] (optional):
   An optional second, third, fourth, etc. array or range of cells to be multiplied. The arrays must have the same length as array1.

Examples

Calculate a weighted average

By multiplying each value by its corresponding weight and taking the sum of those products, then dividing by the sum of the weights, you can calculate a weighted average using SUMPRODUCT.

Calculate a dot product

The dot product of two vectors is the sum of the products of their corresponding elements. You can use SUMPRODUCT to calculate the dot product of two arrays.

Calculate the total revenue for a product line

If you have a table of sales data with units sold and price per unit, you can use SUMPRODUCT to calculate the total revenue for a product line by multiplying units sold by price per unit for each row, then taking the sum of those products.

Common Mistakes

Incorrect number of arguments

One of the most common mistakes when using SUMPRODUCT is providing an incorrect number of arguments. The formula requires at least one array to be provided, but can also accept multiple arrays. If the wrong number of arguments are provided, the formula will return an error. To fix this mistake, double check that the correct number of arrays are being used as inputs.

Mismatched array sizes

Another common mistake when using SUMPRODUCT is providing arrays of different sizes as inputs. When this happens, the formula will return an error. To fix this mistake, double check that all arrays being used as inputs have the same number of rows and columns.

Not using array references

One mistake that users often make is not using array references as inputs for SUMPRODUCT. If individual cells are used instead of an array reference, the formula will not work as intended. To fix this mistake, make sure that all inputs are array references.

Not considering order of arrays

When using multiple arrays as inputs for SUMPRODUCT, it is important to consider the order in which the arrays are listed. If the arrays are in the wrong order, the formula will return incorrect results. To fix this mistake, make sure that the arrays are listed in the correct order for the intended calculation.

Using non-numeric values

Finally, a common mistake when using SUMPRODUCT is including non-numeric values in the arrays being used as inputs. If non-numeric values are included, the formula will return an error. To fix this mistake, check that all values in the arrays being used as inputs are numeric.

Related Formulas

The following functions are similar to SUMPRODUCT or are often used with it in a formula:

• SUM

  The SUM function in Google Sheets adds up a range of numbers. This function is most commonly used to sum the values in a range of cells.

• PRODUCT

  The PRODUCT formula in Google Sheets multiplies all the numbers given in the arguments and returns the product. It is commonly used to calculate the total product of a range of cells.

• AVERAGE

  The AVERAGE function calculates the average (arithmetic mean) of the values passed to it. It is commonly used to find the average of a range of cells containing numerical data.

• ARRAYFORMULA

  The ARRAYFORMULA formula in Google Sheets allows for the application of a formula to an entire column or range of data, without needing to manually copy the formula to each cell. It is most commonly used to simplify and automate complex calculations across large data sets.

• IF

  The IF formula is a logical function used to test a condition and return one value if the condition is true and another value if the condition is false. It is commonly used to create conditional statements and perform calculations based on certain conditions.

Learn More

You can learn more about the SUMPRODUCT Google Sheets function on Google Support.