Function guides ยท Math
SUMPRODUCT function
Multiplies matching items in ranges and adds the products.
Syntax
=SUMPRODUCT(array1, [array2], ...)
| Argument | What it is |
|---|---|
array1 | The first range or array. |
array2, ... | Optional. More ranges of exactly the same size. |
What SUMPRODUCT does
SUMPRODUCT multiplies the numbers in matching positions of two or more ranges, then adds up the results. With two ranges of 3 items each, it does three multiplications and one addition.
That is exactly what a revenue column needs: units times price, summed. It is also how weighted averages work. Because it handles arrays without special entry, it can evaluate conditions inside the formula, which makes it a stand-in for COUNTIFS and SUMIFS when those cannot express the rule. All ranges must have the same size.
Reading a SUMPRODUCT formula becomes simple once you know the pattern. Each comparison in brackets produces a column of TRUE and FALSE. Multiplying columns together keeps only the rows where all are TRUE. Multiplying by a numeric column then carries the values along. Adding the result gives you a sum under several conditions.
SUMIFS is faster and clearer when it can do the job, so reach for SUMPRODUCT when a condition involves a calculation on the criteria range itself, such as the month of a date, the length of text, or an OR across columns. SUMIFS cannot evaluate those directly.
SUMPRODUCT examples
1. Revenue from units and prices
| A | B | C | |
|---|---|---|---|
| 1 | Item | Units | Price |
| 2 | Pens | 10 | 4.5 |
| 3 | Binders | 5 | 12 |
| 4 | Tape | 8 | 3 |
| 5 | |||
| 6 | 129 |
Multiply each unit count by its price and add the results: 10 x 4.5 + 5 x 12 + 8 x 3. That is 45 + 60 + 24, with no helper column needed.
2. Weighted average
| A | B | C | |
|---|---|---|---|
| 1 | Part | Score | Weight |
| 2 | Exam | 80 | 0.6 |
| 3 | Project | 90 | 0.4 |
| 4 | |||
| 5 | 84 |
Score times weight, summed, divided by the total weight. The exam counts for 60 percent and the project 40. (80 x 0.6 + 90 x 0.4) is 84.
3. Count with two conditions
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Region | Sale | ||
| 2 | West | 450 | 2 | |
| 3 | East | 900 | ||
| 4 | West | 620 | ||
| 5 | West | 275 |
Comparisons produce TRUE and FALSE, and multiplying them turns those into 1 and 0. This counts the rows that are West and over 300, much as COUNTIFS would. It also handles tests that COUNTIFS cannot, such as OR conditions with addition.
Common SUMPRODUCT mistakes
- Ranges of different sizes. Every array must have the same dimensions or the result is #VALUE!.
- Text in the number ranges. Text is treated as zero when the arrays are separate arguments. With the multiplication form it causes #VALUE!. Clean the data first.
- Using commas when you meant multiplication.
SUMPRODUCT(A*B)andSUMPRODUCT(A,B)treat text differently. The comma form is the safer default for plain products.
SUMPRODUCT on Excel job tests
The easy version is revenue from a units column and a price column in a single cell. The harder ones ask for a weighted average, or a count or sum with conditions that COUNTIFS and SUMIFS cannot express.
"Without adding a helper column" is a strong hint. The shape for a conditional sum is SUMPRODUCT((condition1)*(condition2)*values).
Make sure the ranges line up row for row. A header row in one range and not the other gives #VALUE! or a result that is off by a row.
Related functions
SUMPRODUCT questions
- What does SUMPRODUCT do?
- It multiplies the items that sit in the same position in each range, then adds the products.
- Can SUMPRODUCT count with conditions?
- Yes. Multiply TRUE and FALSE comparisons together:
=SUMPRODUCT((A2:A9="West")*(B2:B9>300)). - Is SUMPRODUCT an array formula?
- It handles arrays natively, so it does not need Ctrl+Shift+Enter in any Excel version.
- How do I calculate a weighted average?
=SUMPRODUCT(values,weights)/SUM(weights).
Practice SUMPRODUCT
Try it in a working grid: the free lesson COUNTIF: count what matches (module 8 of Everyday Excel) has checked SUMPRODUCT exercises.
Practice this function