Part of the free Module 5: Excel Formulas and Functions · Function 81 of 105 · Full Excel course
The Excel SUMPRODUCT function multiplies corresponding items in two or more arrays and returns the sum of those products. SUMPRODUCT in Excel calculates a total value from quantity and price columns in one step, and because it evaluates arrays natively it also works as a powerful conditional sum or count without array-entry.
SUMPRODUCT syntax
=SUMPRODUCT(array1, [array2], [array3], ...)Arguments
| Argument | Required | Meaning |
|---|---|---|
| array1 | Required | The first range or array whose items you want to multiply and add. |
| array2, array3 … | Optional | Up to 254 further arrays. Every array must have the same dimensions as array1. Non-numeric items are treated as 0. |
SUMPRODUCT returns a number. With a single array it simply behaves like SUM.
Step-by-step example
An order sheet has Quantity in B2:B6 and Unit Price in C2:C6. To get the order total without a helper column:
=SUMPRODUCT(B2:B6, C2:C6)- Excel multiplies B2 by C2, B3 by C3, and so on down the range.
- It adds the five products together.
- The result is identical to =B2*C2+B3*C3+…+B6*C6, but in one formula.
The screenshot below shows SUMPRODUCT multiplying two columns and returning the grand total.
Practical use cases
1. Conditional sum with multiple criteria
=SUMPRODUCT((A2:A100="East")*(B2:B100="Laptop")*D2:D100)Each comparison returns TRUE/FALSE; multiplying converts them to 1/0, so only rows meeting both conditions keep their D value.
2. OR logic that SUMIFS cannot do
=SUMPRODUCT(((A2:A100="East")+(A2:A100="West")>0)*D2:D100)3. Conditional count
=SUMPRODUCT(--(C2:C100>=DATE(2025,1,1)), --(C2:C100<=DATE(2025,3,31)))The double minus (–) converts logical values to numbers. This counts Q1 2025 dates.
4. Weighted average
=SUMPRODUCT(B2:B10, C2:C10) / SUM(C2:C10)B holds scores and C holds weights.
5. Case-sensitive count
=SUMPRODUCT(--EXACT(A2:A100, "ABC"))Common mistakes and errors
- #VALUE! – arrays have different sizes (B2:B10 with C2:C11), or the multiplied array contains text where numbers are expected. Align the ranges; wrap text-containing ranges with N() or use a comma-separated form.
- Whole-column references – SUMPRODUCT(A:A, B:B) evaluates a million rows and slows the workbook. Use bounded ranges or a table.
- Logical arrays returning 0 – passing (A2:A100=”East”) as a separate argument (with a comma) leaves it as TRUE/FALSE, which SUMPRODUCT treats as 0. Multiply it or use — to coerce it.
- Blank cells – treated as 0, which is usually fine for sums but can distort averages.
- Using SUMPRODUCT where SUMIFS suffices – SUMIFS is faster for simple AND criteria; reserve SUMPRODUCT for OR logic, calculations inside criteria or case sensitivity.
Tips and best practices
- Use commas between arrays when possible (SUMPRODUCT(a, b)) rather than a*b; the comma form treats text as 0 instead of returning #VALUE!.
- Coerce logical tests with — and keep each condition in its own set of brackets; the formula is easier to read and to extend.
- Bound the ranges to the data or use an Excel Table. SUMPRODUCT over whole columns evaluates a million rows per formula.
- Reach for SUMIFS first and switch to SUMPRODUCT only when you need OR logic, case sensitivity or a calculation inside the condition.
- Evaluate Formula shows the intermediate arrays, which makes it clear why a condition returns all zeros.
Related functions
- SUMIFS and SUMIF – simpler conditional sums.
- COUNTIFS – conditional counting.
- AVERAGEIF – conditional average.
- N – converts values to numbers inside array formulas.
- Browse the Excel Formulas hub.
Frequently asked questions
What does SUMPRODUCT do in Excel?
It multiplies matching elements of two or more arrays and adds the results. With one array plus logical tests it doubles as a flexible conditional sum or count.
Why does SUMPRODUCT return #VALUE!?
The arrays are not the same size, or a multiplied range contains text. Make every range identical in rows and columns and keep text out of multiplied arrays.
Is SUMPRODUCT better than SUMIFS?
SUMIFS is faster and easier for straightforward AND conditions. SUMPRODUCT wins when you need OR logic, calculations inside the criteria, or case-sensitive matching.
Need a ready-made template? Browse 7,000+ Excel, Power BI and Google Sheets templates at NextGenTemplates.com.