Math & aggregation
How to Use SUMPRODUCT in Excel (Weighted Sums and Counts)
SUMPRODUCT multiplies ranges together cell by cell and then adds up the results. That one idea powers two everyday tasks: weighted totals, like quantity times price, and conditional counts or sums that would otherwise need an array formula.
This guide covers the basic multiply-and-add pattern, weighted averages, and the powerful trick of multiplying TRUE/FALSE conditions to count or sum rows that meet several criteria at once.
SUMPRODUCT syntax
=SUMPRODUCT(array1, [array2], [array3], ...) | Argument | Required | What it does |
|---|---|---|
array1 | Required | The first range or array of numbers. |
array2 | Optional | A second range, multiplied against array1 cell by cell. All arrays must be the same size. |
array3, ... | Optional | Further ranges to multiply in. Add as many as you need, all the same shape. |
Note: With a single array, SUMPRODUCT behaves like SUM. Conditions in parentheses become 1s and 0s when multiplied. Works the same in Google Sheets.
Examples
Real, copy-paste-ready formulas.
1. Weighted sum: quantity times price
=SUMPRODUCT(B2:B10, C2:C10) Multiplies each quantity by its price and adds the line totals in one step, so you do not need a helper column. This is the classic order-total or revenue calculation.
2. Weighted average: grades by credits
=SUMPRODUCT(B2:B10, C2:C10) / SUM(C2:C10) The SUMPRODUCT gives the total weighted points; dividing by the total weight (credits) returns the weighted average, where heavier items count more.
3. Count rows that meet two conditions
=SUMPRODUCT((A2:A100="West")*(B2:B100="Paid")) Each comparison returns TRUE/FALSE; multiplying them turns matches into 1 and everything else into 0, so SUMPRODUCT adds up the rows where both are true.
4. Sum a column with two conditions
=SUMPRODUCT((A2:A100="West")*(B2:B100="Paid")*C2:C100) The two 1/0 condition arrays act as a filter; multiplying by C2:C100 keeps only the matching amounts, which SUMPRODUCT then totals.
How to write SUMPRODUCT step by step
- 1
Click the result cell and type =SUMPRODUCT(
- 2
Select the first range (for a weighted sum, the quantities), then a comma.
- 3
Select the second range to multiply it by (the prices), making sure it is the exact same size.
- 4
For conditional counts, instead wrap each test in parentheses and join them with *, e.g. (A2:A100="West")*(B2:B100="Paid").
- 5
To sum a column under those conditions, multiply by the value range too, e.g. *C2:C100.
- 6
Close the bracket ) and press Enter.
Common errors and fixes
| Error | Why it happens | How to fix it |
|---|---|---|
#VALUE! | The arrays are different sizes, or one contains text where SUMPRODUCT expects numbers. | Make every range the same number of rows and columns, and use the (condition) pattern so text comparisons become 1s and 0s. |
Result is 0 | A condition never matches, often due to a type mismatch or stray spaces in the data. | Check the test values are the same type as the criteria and clean text with TRIM(). |
Counts look doubled or wrong | Using + between conditions (OR logic) when you meant AND, or overlapping criteria. | Use * to require both conditions (AND); use + only when you deliberately want OR and the conditions cannot both be true. |
#REF! | One of the ranges points at deleted cells. | Re-select the ranges so all of them are valid and identically sized. |
Frequently asked questions
When should I use SUMPRODUCT instead of SUMIFS?
Use SUMIFS for straightforward conditional sums; it is simpler to read. Reach for SUMPRODUCT when you need weighted totals (multiplying two columns) or logic SUMIFS cannot do, such as OR conditions or working on calculated values.
Why do I multiply conditions in parentheses?
Each comparison like (A2:A100="West") returns an array of TRUE/FALSE. Multiplying arrays forces them to 1 and 0, so the rows that satisfy every condition become 1 and SUMPRODUCT adds them.
Do I need to press Ctrl+Shift+Enter?
No. SUMPRODUCT handles arrays natively, so you press Enter like a normal formula. That is one of its main advantages over older array formulas.
Does SUMPRODUCT work in Google Sheets?
Yes. The syntax is identical, including the multiply-conditions trick for counting and summing with multiple criteria.