Introduction to the SUMPRODUCT Function
In its simplest form the SUMPRODUCT function multiplies corresponding components in the given arrays and returns the sum of those products.
If you have two arrays of numbers, it will multiply each pair and then sum up those results.
The syntax for SUMPRODUCT is
=SUMPRODUCT(array1, [array2], [array3], ...) |
Where array is the range of cells you want to multiply. Note: arrays must be the same size.
SUMPRODUCT Function Practical Examples
Imagine you run a small bookstore. You have various genres of books, each sold at different prices, and sold in different quantities each month. Our data set for the month looks like this:
Let's navigate through the different
scenarios of SUMPRODUCT using this dataset.
Basic Use of SUMPRODUCT
To find out the total revenue for the month, we'd multiply the units sold of each genre by its price, and then sum those up using this formula:
=SUMPRODUCT(C7:C10, D7:D10) |
Which evaluates to:
=SUMPRODUCT( |
{100;150;110; 90}, |
{20;15;25;18}
) |
Each array is multiplied by the other like so:
And the result of each sum is then added up, resulting in:
Using SUMPRODUCT is a breeze and importantly, doesn’t require the intermediate steps in column E.
SUMPRODUCT with AND criteria
Let's say you want to find out the revenue for books that are priced
above $15 AND sold more than 85 units.
We can use logical tests inside SUMPRODUCT to identify which rows to include in the array!
This is similar to SUMIFS, except you don’t need the intermediary values in column E first.
Using the formula below, we can find the total sales
of books where the Units Sold is > 85 AND the Price is > $15:
=SUMPRODUCT((C7:C10>85)*(D7:D10>15), C7:C10, D7:D10) |
Here, the logical tests return arrays of TRUE and FALSE values:
=SUMPRODUCT( |
{TRUE;TRUE;TRUE;TRUE} |
* |
{TRUE;FALSE;TRUE;TRUE}, |
C7:C10, |
D7:D10 |
) |
And when a math operation is applied to them, in this case multiplication, they convert to their numeric equivalents of 1 and 0 (zero):
=SUMPRODUCT( |
{1;0;1;1}, |
C7:C10, |
D7:D10 |
) |
These 1s and 0s are multiplied by the other two arrays, thus eliminating any values in the corresponding arrays that don’t meet the criteria.
=SUMPRODUCT( |
{1;0;1;1}, |
{100;150;110;90}, |
{20;15;25;18} |
) |
SUMPRODUCT with OR criteria
Now, what if we want to calculate the revenue for books priced above $20 OR sold more than 100 units?
There are two changes required: we use the addition operator (+) to achieve the OR functionality.
And to avoid double counting records, we must wrap the logical tests in the SIGN function.
The SIGN function returns 1 for a positive value, zero (0) if the number is 0, and -1 if the number is negative. Your SUMPRODUCT formula would look like this:
=SUMPRODUCT(SIGN((C7:C10>100) + (D7:D10>20)), C7:C10, D7:D10) |
Evaluating the logical tests results in row 3 returning TRUE for both logical tests:
=SUMPRODUCT( |
SIGN( |
{FALSE;TRUE;TRUE;FALSE} |
+ |
{FALSE;FALSE;TRUE;FALSE}), |
C7:C10, |
D7:D10 |
) |
And
when those tests are added together it results in 2 for the third row:
=SUMPRODUCT(SIGN({0;1;2;0}), C7:C10,
D7:D10) |
Without intervention, this would result in double counting the sales for Sci-Fi. But with the SIGN function, all positive values in the array are converted to 1, resulting in the correct calculation:
=SUMPRODUCT({0;1;1;0}, C7:C10, D7:D10) |
SUMPRODUCT Single Logical Tests
As the months roll by, you keep track of the sales of each genre by date. Suppose for a particular
month, your data now looks like this:
To find out the revenue for books sold after the 2nd of September we can use the following formula:
=SUMPRODUCT( --
(B32:B35>DATE(2023,9,2)),D32:D35,E32:E35) |
The DATE function helps us specify the criteria date and because there is no math operation being performed, we use the double unary
(two minus signs) to coerce the Boolean logical tests into their numeric equivalents of 1 for TRUE and 0 for FALSE.
Note: there is no space between the two minus signs in the double unary in the formula. If you can see a slight gap, that's just the way the font is displayed on yoru screen.
The formula
evaluates like so:
=SUMPRODUCT( -- ({FALSE;FALSE;TRUE;TRUE}),D32:D35,E32:E35) |
And when the double unary is applied it returns:
=SUMPRODUCT({0;0;1;1},D32:D35,E32:E35) |
SUMPRODUCT as an alternative to COUNTIF
Say you want to find out how many genres' book price is less than $20.
We don’t need to aggregate
any values from the table, but rather just count the number of logical tests that return TRUE resulting in a simpler formula:
=SUMPRODUCT(--(D7:D10<20)) |
Again, the double unary coerces the TRUE and FALSE results to their numeric equivalent for SUMPRODUCT to aggregate:
SUMPRODUCT as an alternative to AVERAGEIFS
You're curious about the average price of books sold in quantities greater than 100 units.
=SUMPRODUCT(--(C7:C10>100),C7:C10,D7:D10)/SUMPRODUCT(--(C7:C10>100),C7:C10) |
The numerator gives us the total price of books that meet the criteria, and the denominator gives the
count of such books. Dividing them gives the average.
Quick Recap, Rules, and Limitations
In SUMPRODUCT functions you can employ the AND logic, and OR logic using the * and + symbol:
- When the multiplication symbol * is used it reads ‘AND’.
- When the plus symbol + is used it reads ‘OR’.
- If the OR criteria
reference different columns, you should wrap the logical tests in the SIGN function to avoid double counting.
- If you only have one logical test, use the double unary to coerce the Boolean TRUE and FALSE values to their numeric equivalents.
- Each array referenced in SUMPRODUCT must always be the same size.
- Text values included in arrays referenced by SUMPRODUCT will be treated as zero.
- SUMPRODUCT can be resource-intensive, especially with large data
sets.
SUMPRODUCT Function Benefits
The SUMPRODUCT function is a powerhouse! It's versatile, efficient, and can replace many other functions when used creatively.
We hope our bookstore example shed light on its applications. Next time you find
yourself reaching for a SUMIFS or AVERAGEIFS, consider giving SUMPRODUCT a shot!
