SUMPRODUCT practice exercises
Multiply two columns row by row and add up the results in one formula: order totals, weighted averages and sums with conditions written as tests.
How SUMPRODUCT works
SUMPRODUCT multiplies the values that sit in the same position in two or more ranges, then adds up the results. Each range is one argument, and all the ranges must be the same size.
=SUMPRODUCT(B2:B6,C2:C6) multiplies each row's units by its price and adds the products, so you get the order total without a helper column. Divide by a sum and you have a weighted average: =SUMPRODUCT(B2:B5,C2:C5)/SUM(C2:C5) counts each score as many times as its weight. A test inside the formula turns it into a conditional sum: =SUMPRODUCT((A2:A7="North")*B2:B7*C2:C7) gives the revenue for the North rows only.
Use SUMIFS when you only need to add up one column under conditions. Reach for SUMPRODUCT when the numbers must be multiplied first. The exercises below cover an order total, a weighted average, and revenue for one region. You don't need Ctrl+Shift+Enter, because SUMPRODUCT handles the arrays itself.
- 1Order total with SUMPRODUCTIn F1, work out the order total in one formula without a helper column. Use SUMPRODUCT to multiply the units by the prices row by row and add up the results.
- 2A weighted average with SUMPRODUCTPremiumIn F1, work out the weighted average score, where each score counts as many times as its weight. Divide the SUMPRODUCT of scores and weights by the SUM of the weights.
- 3Revenue for one region with SUMPRODUCTPremiumIn F1, work out the revenue (units × price) for the North rows only, in one formula. Multiply a test on the region by the two columns inside SUMPRODUCT: SUMPRODUCT((A2:A7="North")*B2:B7*C2:C7).
Functions in this topic
Read the guide
Questions
How do I use SUMPRODUCT with multiple conditions?
Multiply the tests: =SUMPRODUCT((A2:A7="North")*(B2:B7="Shoes")*C2:C7) adds column C where the region is North and the product is Shoes. Multiplying means AND. For OR, add the tests instead, as in ((A2:A7="North")+(A2:A7="South")). Put each test in its own parentheses so the comparison happens before the multiplication. A test returns TRUE or FALSE, and multiplying turns those into 1 and 0.
Why does SUMPRODUCT return #VALUE!?
The ranges are different sizes, such as B2:B6 and C2:C7. They need the same number of rows and columns. Text in the data can also cause it when you join the ranges with *, because text can't be multiplied. With commas between the ranges, text is treated as zero instead.
What is the difference between SUMIFS and SUMPRODUCT?
SUMIFS adds up one column for the rows that meet your conditions and is easier to read. SUMPRODUCT can multiply columns first, such as units times price, and can test conditions that SUMIFS can't express, such as a calculation on each row. For plain conditional sums, SUMIFS is the better choice.