Expires in:
This article walks through a practical example of Excel's SUMPRODUCT function using a transportation matrix of factories and distribution centers. You will learn what SUMPRODUCT does, how to set up the arrays, and how to avoid common mistakes. It is aimed at Excel users who want a clear, hands-on explanation they can replicate in their own workbooks.
What You'll Learn / Key Take-Aways
- What the SUMPRODUCT function does: multiply corresponding elements of arrays then sum the results.
- How to apply SUMPRODUCT to a transportation matrix to compute total shipping cost in one formula.
- How to prepare your arrays so SUMPRODUCT returns the expected result.
- Common mistakes to avoid, including mismatched ranges and unintended relative references.
## What is SUMPRODUCT and when to use it
SUMPRODUCT is an Excel function that multiplies corresponding elements in two or more arrays and then sums those products. It is particularly useful when you need the result of multiple pairwise multiplications without writing intermediate formulas for each cell.
Definition of terms
- Array: A range of cells arranged in rows and columns that you want SUMPRODUCT to process. Each array must have the same dimensions.
- Transportation matrix: A grid that shows either costs, quantities, or both for shipping between origins and destinations. In our example we use one matrix for cost per unit and a matching matrix for units shipped.
Use cases for SUMPRODUCT
- Calculating weighted averages.
- Summing conditional products when you have numeric criteria in matrix form.
- Replacing helper columns where you would otherwise multiply and then sum manually.
## The transportation matrix example - what we have and what we want
In the example we use:
- Four factories (Boston, New York, Chicago, Oakland).
- Four distribution centers (Miami, Dallas, Seattle, Baltimore).
- A top matrix with shipping cost per unit from each factory to each distribution center.
- A bottom matrix with the quantity of units shipped between each factory and distribution center.
Objective
Calculate the total shipping cost with one formula in a single cell. The total cost equals the sum of (units shipped x cost per unit) across the entire matrix.
## Step-by-step: Build the SUMPRODUCT formula
1. Open the workbook and locate the two matrices. For this example, costs are in D5:G8 and units are in D13:G16.
2. Ensure both ranges have the same dimensions: the same number of rows and columns. SUMPRODUCT requires matching array sizes.
3. Click the cell where you want the total cost, for example E18.
4. Type =SUMPRODUCT( and then select the first array (the cost matrix). For example: D5:G8
5. Type a comma, then select the second array (the units matrix). For example: D13:G16
6. Close the parentheses and press Enter.
Example formula
=SUMPRODUCT(D5:G8, D13:G16)
What happens behind the scenes
- SUMPRODUCT multiplies each cell in the first array by the corresponding cell in the second array. For instance, Boston-to-Miami cost in D5 is multiplied by Boston-to-Miami units in D13.
- After computing all pairwise products, SUMPRODUCT sums those results and returns the grand total.
Why this is better than helpers
You can arrive at the same number by multiplying each pair in a helper grid and summing those results. However SUMPRODUCT collapses that into one readable formula and reduces workbook clutter. It is also less error prone because you avoid many intermediate formulas.
## When to lock references and when not to
- If your arrays are fixed locations you can use absolute references so copying the formula does not shift them. In many dashboard or summary scenarios you do not need to copy the formula so simple relative references often suffice.
- If you plan to copy the formula or convert ranges to tables, use absolute references or structured references to keep the formula stable.
## Variations and more advanced uses
- Multiple arrays: SUMPRODUCT can accept more than two arrays. Excel multiplies the corresponding elements across all arrays before summing.
- Conditional sums: You can emulate conditional logic by multiplying by logical expressions. For example: =SUMPRODUCT((RangeA=Criteria)*RangeB*RangeC). Note that logical tests return TRUE/FALSE which are coerced to 1/0 when multiplied.
- Weighted averages: SUMPRODUCT can compute weighted averages by dividing the SUMPRODUCT of weights and values by the SUM of weights.
## Additional Tips, Pitfalls to Avoid & Pro Advice
- Mismatched ranges: SUMPRODUCT will return a #VALUE error if the arrays do not match in size. Always check row and column counts.
- Hidden non-numeric values: If a cell in an array contains text, SUMPRODUCT may return unexpected results or errors. Clean data first.
- Relative references: If you drag a SUMPRODUCT formula and the arrays shift, you may get incorrect results. Use absolute references like $D$5:$G$8 when needed.
- Excel Tables: SUMPRODUCT works with structured references but be mindful of the reference syntax. Tables can make formulas easier to read and maintain.
- Performance: SUMPRODUCT calculates element by element. Extremely large ranges may slow calculation times. Limit arrays to the exact ranges you need.
## FAQ
Q: What does SUMPRODUCT do in plain English?
A: SUMPRODUCT multiplies corresponding values in two or more ranges and then adds all those products together. Think multiply then add.
Q: Can SUMPRODUCT handle more than two arrays?
A: Yes. SUMPRODUCT accepts multiple arrays. Excel multiplies corresponding elements across all arrays before summing.
Q: Why am I getting a #VALUE error with SUMPRODUCT?
A: The most common reason is mismatched range sizes. Make sure each array has the same number of rows and columns. Also check for text inside numeric ranges.
Q: Can I use SUMPRODUCT for conditional sums like SUMIF?
A: Yes. Multiply ranges by logical expressions that evaluate to 1 or 0. For example: =SUMPRODUCT((RegionRange="East")*QuantityRange*PriceRange).
Q: Is SUMPRODUCT faster than helper columns?
A: It depends. SUMPRODUCT reduces visible formulas and is often easier to maintain, but for very large data sets helper columns or database functions may perform better.
## Summary of Key Points / Take-Home Messages
- SUMPRODUCT multiplies corresponding cells across arrays and sums the products.
- Ensure arrays are the same size and contain numeric values where expected.
- SUMPRODUCT is ideal for matrix calculations like total shipping cost in a transportation matrix.
- Use absolute references when you need the formula to stay fixed and watch out for performance on large ranges.
Use the step-by-step example above to replace intermediate multiplication columns with a single, auditable formula that calculates total cost directly.
