FormuladrillSUMPRODUCT
SUMPRODUCT(array1, [array2], ...) multiplies the arrays element by element and adds the products, so =SUMPRODUCT(B2:B10, C2:C10) is quantity times price summed in one step. Because it handles arrays natively it also sums or counts with conditions, such as =SUMPRODUCT((A2:A100="North")*C2:C100).
Syntax
SUMPRODUCT(array1, [array2], [array3], ...)| Part | What it is |
|---|---|
array1 | The first range or array. |
array2, array3... | More ranges of the same size. They are multiplied with array1 position by position. |
Examples
=SUMPRODUCT(B2:B4, C2:C4)Result: 32 when B2:B4 is 2, 3, 4 and C2:C4 is 5, 4, 3 (2*5 + 3*4 + 4*3)
=SUMPRODUCT(B2:B10, C2:C10) / SUM(B2:B10)Result: Weighted average of C with the weights in B
Portfolio return, average price per unit, weighted grade.
=SUMPRODUCT((A2:A100="North") * C2:C100)Result: Total of C for North rows, the same as SUMIF but usable in closed workbooks and with other math
=SUMPRODUCT(--(A2:A100="North"), --(B2:B100="Shipped"))Result: Count of rows that are both North and Shipped
The double minus turns TRUE and FALSE into 1 and 0.
Common mistakes
- #VALUE!: the arrays are different sizes. SUMPRODUCT(B2:B10, C2:C11) fails; make them match.
- The result is 0 with conditions written as commas: SUMPRODUCT((A2:A100="North"), C2:C100) multiplies TRUE and FALSE, which stay TRUE and FALSE. Use * between them or -- on the condition.
- Text in a numeric column is treated as 0, so a column with numbers stored as text undercounts silently.
- Whole-column references (A:A) work but are slow because every row is multiplied; limit the range.
Practice SUMPRODUCT
These drills check your answer on live data, free, no account needed.
- Portfolio returnWeights and returns are listed for three holdings. Calculate the weighted portfolio return in C5 with a single formula.
Questions
What does SUMPRODUCT do in simple terms?
It multiplies two or more lists pairwise and adds up the results. Quantity times price for every line, totaled, is the classic use.
How do I do a weighted average in Excel?
=SUMPRODUCT(values, weights) / SUM(weights). For a portfolio: =SUMPRODUCT(returns, amounts) / SUM(amounts).
SUMPRODUCT or SUMIFS?
SUMIFS is faster and simpler for plain conditions. SUMPRODUCT is needed when you also multiply, when conditions involve OR across columns, or when you need calculations on the criteria range first.
Related
- SUMIFSHow to use SUMIFS in Excel to add values that meet two or more conditions, with examples for region and status, date ranges, not-equal and wildcards.
- SUMIFHow to use SUMIF in Excel to add up numbers that match one criterion, with examples for text, greater than, cell references and wildcards, and common fixes.
- COUNTIFSHow to use COUNTIFS in Excel to count rows where two or more conditions are all true, with examples for text plus number, date ranges and not-equal.
- AVERAGEIFHow to use AVERAGEIF and AVERAGEIFS in Excel to average only the cells that match a condition, with examples for one region, positive values and two criteria.