SUMPRODUCT multiplies arrays together and sums the results. One of the most versatile formulas in Excel — weighted averages, conditional sums, unique counts and more.
SUMPRODUCT multiplies corresponding values from two or more arrays together, then sums all the results. The simplest use is =SUMPRODUCT(quantities, prices) — it multiplies each quantity by its price and adds up the totals, giving you total revenue in one formula instead of needing a helper column.
But SUMPRODUCT's real power goes far beyond this. Because it works on entire arrays at once, it can be used for conditional sums (replacing SUMIFS in some cases), weighted averages, counting unique values, and complex multi-criteria calculations — often without needing Ctrl+Shift+Enter array entry.
=SUMPRODUCT(array1, [array2], [array3], ...)| Argument | Description |
|---|---|
| array1 required | The first array or range to multiply. |
| [array2] ... optional | Additional arrays of the same size. Up to 255 arrays. |
All arrays must have exactly the same number of rows and columns. Mismatched sizes return #VALUE!.
=SUMPRODUCT(B2:B10, C2:C10)Multiplies quantity in B by price in C for each row, then sums all the results. No helper column needed.
=SUMPRODUCT(scores, weights) / SUM(weights)Multiplies each score by its weight, sums the products, divides by total weight. More accurate than a simple average when items have different importance.
=SUMPRODUCT((A2:A100="North")*(B2:B100="Coffee")*C2:C100)Multiplying boolean arrays (TRUE/FALSE → 1/0) creates AND logic. Only rows where both conditions are true contribute to the sum.
=SUMPRODUCT(1/COUNTIF(A2:A100, A2:A100))Each value is counted how many times it appears. 1/count gives each unique value a fractional weight that sums to 1. Total = number of distinct values.
=SUMPRODUCT(((A2:A100="North")+(A2:A100="South")>0)*C2:C100)Adding boolean arrays creates OR logic. The >0 converts any positive sum to 1, ensuring rows matching either condition count once only.
SUMPRODUCT can replicate SUMIFS using array multiplication. The advantage: SUMPRODUCT handles OR logic natively and works in older Excel versions that predate SUMIFS.
SUMIFS equivalent (AND logic):
=SUMPRODUCT((A2:A100="North")*(B2:B100>0)*C2:C100)
OR logic (impossible with single SUMIFS):
=SUMPRODUCT(((A2:A100="North")+(A2:A100="South")>0)*C2:C100)
| SUMPRODUCT | SUMIFS | |
|---|---|---|
| AND logic | ✅ (multiply arrays) | ✅ (native) |
| OR logic | ✅ (add arrays) | ❌ (not native) |
| Speed on large data | Slower | Faster |
| Weighted calculations | ✅ | ❌ |
| Works in Excel 2003 | ✅ | ❌ |
ExcelPro has SUMPRODUCT exercises in the Accounting and Data Analyst tracks. Free to start.
Try exercises →