SUMPRODUCT multiplies matching positions across ranges and then adds the results. It is ideal for totals such as quantity times unit price without creating a helper column for every row.
Does this affect you?
Use this for Excel for Microsoft 365, Excel 2021, Excel 2019, and Excel for the web when you need weighted totals, row-by-row multiplication, or conditional totals in one formula.
Multiply two ranges and add the results
This is the classic SUMPRODUCT use.
- Click an empty cell for the total.
- Type =SUMPRODUCT(.
- Select the first range, such as quantities in B2:B20.
- Add a comma and select the matching second range, such as prices in C2:C20.
- Close the parenthesis, creating =SUMPRODUCT(B2:B20,C2:C20).
- Press Enter.
Excel multiplies B2 by C2, B3 by C3, and so on, then adds every row result into one total.
Add a condition
Use conditions to include only matching rows.
- Start with =SUMPRODUCT(.
- Add a test such as (A2:A20=”West”).
- Multiply that test by the numeric ranges, such as *B2:B20*C2:C20.
- Close the formula, for example =SUMPRODUCT((A2:A20=”West”)*B2:B20*C2:C20).
- Press Enter.
The condition becomes 1 for matching rows and 0 for other rows, so non-matching rows do not contribute to the total.
More control
Use multiple conditions
Add another condition with another asterisk, such as region equals West and status equals Closed. All conditions must be true for a row to count.
Count rows with SUMPRODUCT
If you only multiply conditions and omit the value ranges, SUMPRODUCT returns a count of rows matching those conditions.
Fix #VALUE errors
All ranges must be the same shape. A formula comparing B2:B20 with C2:C21 will fail because the ranges do not have the same number of rows.
Use SUMIFS when it reads better
For straightforward criteria-based totals without row-by-row multiplication, SUMIFS is usually clearer and can be faster in very large workbooks.
Sources
- Microsoft Support – SUMPRODUCT function (2025)
- Microsoft Support – SUMIFS function (2025)
- Microsoft Support – Overview of formulas in Excel (2024)