How to Use SUMPRODUCT in Excel
Multiplies matching items across arrays and adds the results — a multi-condition SUM that works in every Excel version.
What is SUMPRODUCT in Microsoft Excel?
SUMPRODUCT multiplies values position by position across two or more arrays, then sums the products. Written with boolean arrays it becomes a powerful conditional calculator that predates SUMIFS and still handles logic SUMIFS cannot, such as OR conditions and array math.
Formula Syntax
Arguments Breakdown
| Argument | Requirement | Purpose & Details |
|---|---|---|
| array1 | Required | The first array or range. Its dimensions must match every other array. |
| array2 | Optional | The second array. SUMPRODUCT multiplies array1 by array2 element by element, then adds the products. |
When to Use SUMPRODUCT (Real-World Business Scenarios)
- 1
Calculating weighted totals such as quantity × rate across an invoice without adding a helper column.
- 2
Summing with OR logic: =SUMPRODUCT((A2:A9="North")+(A2:A9="South"),C2:C9).
- 3
Counting or summing across two conditions in older Excel versions that lack SUMIFS or FILTER.
Step-by-Step Practical Business Example
Suppose you have the following dataset in Excel cells A1:D4:
| Product | Qty | Rate |
|---|---|---|
| Laptop | 2 | 55000 |
| Mouse | 10 | 1200 |
| Keyboard | 5 | 2500 |
How it works: Multiplies 2×55,000, 10×1,200 and 5×2,500, then adds all three products to give the invoice total of 134,500.
Common Errors & How to Fix Them
Why it happens: The arrays have different dimensions, for example 5 rows against 6 rows, so Excel cannot pair them.
Fix: Make every range the same height and width, and avoid whole-column references that include a different number of used rows.
Why it happens: Text was used where a number was expected, or boolean conditions were written without parentheses.
Fix: Wrap each condition: =SUMPRODUCT((A2:A9="North")*1,C2:C9), and confirm the value column is numeric.
Try SUMPRODUCT Live in Your Browser
No need to launch desktop Excel. Practice formulas with real-time feedback in our HyperFormula sandbox.
Frequently Asked Questions (SUMPRODUCT)
What does the SUMPRODUCT formula do in Excel?
SUMPRODUCT multiplies values position by position across two or more arrays, then sums the products. Written with boolean arrays it becomes a powerful conditional calculator that predates SUMIFS and still handles logic SUMIFS cannot, such as OR conditions and array math.
What is the syntax for SUMPRODUCT?
What are common mistakes when using SUMPRODUCT?
#VALUE!: The arrays have different dimensions, for example 5 rows against 6 rows, so Excel cannot pair them. (Make every range the same height and width, and avoid whole-column references that include a different number of used rows.) Result is 0: Text was used where a number was expected, or boolean conditions were written without parentheses. (Wrap each condition: =SUMPRODUCT((A2:A9="North")*1,C2:C9), and confirm the value column is numeric.)
Related Excel Formulas
Keep building your formula toolkit with these math & stats functions.
- SUMIFSSums numerical values across multiple conditions (e.g. Sales in North Region for Q1).
- COUNTIFSCounts the number of rows that satisfy multiple simultaneous criteria.
- SUMAdds numbers across cells, ranges and entire columns — the first formula most people ever type in Excel.
- SUMIFAdds only the numbers that match one condition — the fastest way to total a region, product or status without a Pivot Table.
- COUNTIFCounts how many cells match a condition — duplicates, blanks, specific text, or values above a limit.
- AVERAGEReturns the arithmetic mean of a range while automatically ignoring blanks and text.
Master SUMPRODUCT in Advanced Excel
Learn SUMPRODUCT along with our formula library, practice files, and verifiable certificates in our complete curriculum.