Google Sheets Function: SUMPRODUCT

The SUMPRODUCT function calculates the sum of products of two or more ranges of values multiplied together, row by row.


=SUMPRODUCT(array1, array2)


=SUMPRODUCT(array1, array2, array3, ...)

Example of use

To better understand the SUMPRODUCT function and what "calculation of the sum of products" means, here is a simple table to start with:

google sheets function sum sumproduct

In this table, the total costs are the products of columns B and C =B2*C2, and the TOTAL is the sum of the various products =SUM(D2:D11).

The goal now is to achieve the same result using SUMPRODUCT and without the total cost column:

google sheets total table sumproduct

Enter in the SUMPRODUCT function, the 2 cell ranges containing the data to multiply with each other (row by row) and for which you want to obtain the sum:

google sheets function sumproduct

The following formula indeed returns the sum of products as in the first table:

google sheets function sumproduct sum total

Example of use with 3 ranges

By adding a third range, the SUMPRODUCT function will then return the sum of the products of the 3 ranges.

For example, by adding a column with checkboxes, only the rows with a checked box will be accounted for:

google sheets function sumproduct checkboxes
A checkbox returns either TRUE (= 1) or FALSE (= 0), so each row is multiplied by 1 or 0 depending on the checkbox.

Example of use with a condition

To account for a range based on a condition (instead of checkboxes in the previous example), enter the range followed by the condition.

For example, to only account for rows where the quantity is greater than 100, enter:

google sheets function sumproduct condition

Just as with the checkboxes, this third range will return TRUE (= 1) or FALSE (= 0) depending on the condition >100, and will allow only the rows meeting this criteria to be counted.

If needed, you can copy the Google Sheets document (or view the document) with these examples.