Excel Function: SUMPRODUCT
The SUMPRODUCT function returns the sum of ranges of values multiplied by each other, row by row.
=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
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:
Note that the same result can be obtained with the formula:
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:
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
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:
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
The formula then is:
To now sum the points whose result is
<20, the formula becomes:
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
>20, it will not be enough to reverse the tests.
The following formula will not return the expected result:
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
The formula therefore becomes: