SUMPRODUCTformula in Google Sheets multiplies corresponding values in the specified arrays and returns the sum of those products. It is commonly used to find the weighted average or to perform a dot product calculation.
- How to use
- Examples of using
SUMPRODUCTformula not working?
- Similar formulas to
SUMPRODUCT formula with the syntax shown below, it has 1 required parameter and 1 optional parameter:
- array1 (required):
The first array or range of cells to be multiplied. The arrays must have the same length.
- [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.
ExamplesHere are a few example use cases that explain how to use the
SUMPRODUCTformula in Google Sheets.
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
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.
SUMPRODUCTnot working? Here are some common mistakes people make when using the
SUMPRODUCTGoogle Sheets Formula:
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.
The following functions are similar to
SUMPRODUCT or are often used with it in a formula:
SUMfunction in Google Sheets adds up a range of numbers. This function is most commonly used to sum the values in a range of cells.
PRODUCTformula 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.
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.
ARRAYFORMULAformula 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.
IFformula 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.
You can learn more about the
SUMPRODUCT Google Sheets function on Google Support.