How to Use SUMPRODUCT in Excel (and Google Sheets)
1. What Is SUMPRODUCT?
SUMPRODUCT multiplies corresponding elements in two or more arrays and then adds the results.
In practice it lets you compute a weighted sum or a conditional total without creating helper columns.
| Excel syntax | Google Sheets syntax |
|---|---|
=SUMPRODUCT(array1, [array2], …) |
=SUMPRODUCT(array1, [array2], …) |
- array1, array2, … – Ranges or literal arrays that contain numbers.
- All arrays must have the same dimensions (identical row‑ and column‑counts).
- Non‑numeric values are treated as 0, so they don’t break the calculation.
Why Use It?
| Use case | How SUMPRODUCT helps |
|---|---|
| Weighted totals (price × quantity) | One‑line calculation, no extra columns |
| Conditional sums (sum only when a condition is met) | Embed logical tests (--(criteria)) directly |
| Dot‑product / cross‑tab calculations | Computes vector products in a single formula |
Version note –
SUMPRODUCThas been in Excel since 2000 and in Google Sheets from the start, so it works in every modern desktop or online version. No subscription or special edition is required.
2. Basic Syntax
=SUMPRODUCT(array1, [array2], [array3], …)
If only one array is supplied, SUMPRODUCT simply adds the values (behaving like SUM).
If the arrays differ in size, Excel returns #VALUE!.
2.1 Simple Multiplication
| A | B |
|---|---|
| 2 | 5 |
| 3 | 7 |
| 4 | 9 |
=SUMPRODUCT(A2:A4, B2:B4)
Result: 2*5 + 3*7 + 4*9 = 67
2.2 Adding a Constant Multiplier
| Qty | Price |
|---|---|
| 10 | 12 |
| 5 | 20 |
| 8 | 15 |
=SUMPRODUCT(A2:A4, B2:B4, 0.9)
Result: (10*12 + 5*20 + 8*15) * 0.9 = 486
2.3 Conditional “Filter” Trick
| Item | Units | Unit Price |
|---|---|---|
| Apple | 30 | 0.50 |
| Banana | 20 | 0.30 |
| Apple | 15 | 0.55 |
| Orange | 10 | 0.60 |
Sum revenue for Apple only:
=SUMPRODUCT((A2:A5="Apple") * B2:B5 * C2:C5)
Result: 30*0.50 + 15*0.55 = 23.25
Google Sheets: Identical syntax works the same way.
3. Conditional Calculations
SUMPRODUCT shines when you need to sum values only if one or more criteria are satisfied. Convert each logical test to an array of 1’s (TRUE) and 0’s (FALSE), then multiply by the numeric data.
3.1 Two‑Condition Example
| Region | Product | Units | Unit Price |
|---|---|---|---|
| East | Widget | 10 | 12.50 |
| West | Gizmo | 5 | 20.00 |
| East | Gizmo | 8 | 20.00 |
| South | Widget | 7 | 12.50 |
| East | Widget | 4 | 12.50 |
Goal: Revenue for East and Widget.
=SUMPRODUCT((A2:A6="East") *
(B2:B6="Widget") *
C2:C6 *
D2:D6)
Result: 187.5 ( (10 × 12.50) + (4 × 12.50) )
3.2 Adding a Date Range
Assume column E contains dates.
=SUMPRODUCT((A2:A100="East") *
(B2:B100="Widget") *
(E2:E100>=DATE(2024,1,1)) *
(E2:E100<=DATE(2024,3,31)) *
C2:C100 *
D2:D100)
The extra two logical arrays restrict the sum to Jan 1 – Mar 31 2024.
4. Advanced Tips & Common Pitfalls
| Tip | Explanation |
|---|---|
| Boolean math instead of helper columns | TRUE = 1, FALSE = 0, so SUMPRODUCT((A2:A10="East")*(B2:B10>100)) counts rows that meet both criteria. |
| Avoid whole‑column references in older Excel | =SUMPRODUCT(A:A,B:B) forces Excel to process >1 million rows, which can freeze the workbook. Use explicit ranges (A2:A1000). |
| Watch mixed data types | Text that looks like a number ("10") is treated as 0. Clean data or wrap the range in VALUE() if needed. |
Double‑unary (--) for explicit conversion |
--(A2:A5="Apple") forces TRUE/FALSE to 1/0; useful when the logical test is part of a larger expression. |
| Array‑formula entry | In Excel 2003 or earlier you needed Ctrl+Shift+Enter. Modern versions calculate automatically. |
5. Combining SUMPRODUCT with Other Functions
5.1 SUMPRODUCT + IF (array‑style conditional sum)
| Item | Qty | Price |
|---|---|---|
| Apple | 10 | 0.50 |
| Banana | 5 | 0.30 |
| Apple | 7 | 0.55 |
| Orange | 3 | 0.80 |
=SUMPRODUCT((A2:A5="Apple")*B2:B5*C2:C5)
Result: 10*0.50 + 7*0.55 = 10.85
5.2 SUMPRODUCT + -- (double‑unary)
=SUMPRODUCT(--(A2:A5="Apple"), B2:B5, C2:C5)
Produces the same 10.85 result; the -- makes the conversion explicit.
5.3 SUMPRODUCT + ISNUMBER + SEARCH (text‑contains filter)
| Description | Amount |
|---|---|
| Online sale | 120 |
| In‑store sale | 80 |
| Online refund | -20 |
| In‑store refund | -10 |
=SUMPRODUCT(--ISNUMBER(SEARCH("sale", A2:A5)), B2:B5)
Result: 120 + 80 = 200
Note: SEARCH is case‑insensitive in both Excel and Google Sheets; use FIND for case‑sensitive matching.
6. FAQ
What does the SUMPRODUCT function do?
It multiplies corresponding elements in two or more equal‑sized arrays and then returns the sum of those products. This makes it ideal for weighted totals, dot products, and conditional aggregations.
Can I use SUMPRODUCT for conditional sums?
Yes. By turning logical tests into 1/0 arrays (e.g., (A2:A10="East")), you can multiply those arrays by the values you want to sum, letting SUMPRODUCT return the total that meets the condition(s).
How is SUMPRODUCT different from SUMIF?
SUMIF handles a single condition and requires a separate range for the criteria. SUMPRODUCT can evaluate multiple conditions in one formula, works with any number of arrays, and does not need a dedicated “sum range” argument.
What versions of Excel support SUMPRODUCT?
All desktop versions from Excel 2000 onward and all Microsoft 365 builds include SUMPRODUCT. No special add‑ins or subscriptions are needed.
Is SUMPRODUCT available in Google Sheets?
Yes. Google Sheets implements the same syntax and behavior, so formulas written for Excel work unchanged in Sheets.
Now you have a clear, version‑agnostic guide to using SUMPRODUCT for simple multiplications, weighted totals, and powerful conditional calculations.