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))