# Excel Function: SUMPRODUCT

The SUMPRODUCT function returns the sum of ranges of values multiplied by each other, row by row.

Usage:

`=SUMPRODUCT(range_1)`

or

`=SUMPRODUCT(range_1, range_2, ...)`

## SUMPRODUCT usage example

To better understand the operation of SUMPRODUCT and what its definition of "sum of products" means, here is a simple example to start with:

In this first table, the total costs in column D are obtained by multiplying the values in columns B and C using the formula `=B2*C2` and the total is simply the sum of these different products `=SUM(D2:D11)`.

The goal now is to calculate the sum of these different products directly (without a "Total cost" column):

To do this, insert the SUMPRODUCT function and then add the 2 ranges of cells containing the data to be multiplied by each other (row by row) and for which you calculate the sum:

The SUMPRODUCT function indeed returns the same total as in the first example:

``=SUMPRODUCT(B2:B11,C2:C11)``

Note that the same result can be obtained with the formula:

``=SUMPRODUCT((B2:B11)*(C2:C11))``

In this case, the product of the 2 ranges `(B2:B11)*(C2:C11)` is calculated and returned as a single range for which the SUMPRODUCT function returns the sum.

## SUMPRODUCT with a condition

To perform the same calculation but only for items with a quantity greater than 100, first copy the last formula and add the condition `>100` to it.

The formula then becomes:

``=SUMPRODUCT((B2:B11>100)*(C2:C11))``

This formula does not return the expected result because `(B2:B11>100)` does not contain any quantity, it is instead a range that returns 1 (TRUE) or 0 (FALSE) depending on the `>100` test.

It is equivalent to the previous SUMPRODUCT function (without condition) with these values:

In this case, you therefore need to add the condition in addition to the other 2 ranges:

``=SUMPRODUCT((B2:B11)*(C2:C11)*(B2:B11>100))``

Here, the quantity is multiplied by the cost per unit and then by 0 or 1 depending on the test (the products that should not be taken into account will be multiplied by 0 and therefore will not modify the sum).

This time, the SUMPRODUCT function correctly returned the sum of the products taking into account the condition:

## SUMPRODUCT with multiple conditions

To simplify the formula a bit (before complicating it), the SUMPRODUCT function will here need to return the sum of the players' points with a result `>100`.

The formula then is:

``=SUMPRODUCT((C2:C11)*(B2:B11>100))``

To now sum the points whose result is `>100` or `<20`, the formula becomes:

``=SUMPRODUCT((C2:C11)*((B2:B11>100)+(B2:B11<20)))``

To better understand `((B2:B11>100)+(B2:B11<20))`, remember that a test returns 0 or 1.

In this case, the ranges are added to obtain the range `((B2:B11>100)+(B2:B11<20))` which is the result of the addition of the 0s and 1s of the 2 ranges:

If we now reverse the conditions to sum the points whose result is `<=100` and `>20`, it will not be enough to reverse the tests.

The following formula will not return the expected result:

``=SUMPRODUCT((C2:C11)*((B2:B11<=100)+(B2:B11>=20)))``

For it to work, you need to add a condition to the range `((B2:B11<=100)+(B2:B11>=20))` to only keep those who have passed the 2 tests (thus those `=2`).

The formula therefore becomes:

``=SUMPRODUCT((C2:C11)*(((B2:B11<=100)+(B2:B11>=20))=2))``