SUMPRODUCT function
SUMPRODUCT multiplies matching items in two or more ranges and adds the results. It gives revenue from units and price columns, weighted averages, and conditional totals, all in one cell and without Ctrl+Shift+Enter in any version.
- Available in
- Every current version
- Category
- Math functions
- Returns
- The sum of the row-by-row products.
- Google Sheets
- Works the same in Google Sheets.
Syntax
=SUMPRODUCT(array1, [array2], ...)
| Argument | Required? | What it is |
|---|---|---|
array1 | Required | The first range of numbers, such as units. |
array2 | Optional | More ranges the same size, such as prices. Each row is multiplied together before adding. |
Examples
Each result below is calculated from the sample sheet by our formula engine, the same one that grades the practice.
| Cell | Formula | Result | What it shows |
|---|---|---|---|
| F2 | =SUMPRODUCT(B2:B5,C2:C5) | $6,585 | Total revenue: units × price for every row, added up |
| F3 | =SUMPRODUCT((D2:D5="East")*B2:B5*C2:C5) | $3,820 | Revenue for East only: the test returns 1 or 0 for each row |
| F4 | =SUMPRODUCT(B2:B5,C2:C5)/SUM(B2:B5) | $61.54 | Average price weighted by units sold |
Try it live
This is the sample sheet. Change any number or text, or edit a formula, and the results update.
Show the sample data as a table
| Product | Units | Price | Region | ||
| Widget | 40 | $52 | East | $6,585 | |
| Gadget | 25 | $89 | West | $3,820 | |
| Gizmo | 12 | $145 | East | $61.54 | |
| Doohickey | 30 | $18 | West |
Practice SUMPRODUCT
From lesson 24, SUMPRODUCT. Graded instantly, then re-checked on changed data.
Total revenue
G2, calculate total revenue (units × price for every row) in one formula.Common mistakes
| What you see | Why, and how to fix it |
|---|---|
| #VALUE! | The ranges are different sizes, such as B2:B5 and C2:C6. Make every range cover the same rows. |
| The result is 0 | A condition passed as its own argument, as in =SUMPRODUCT(D2:D5="East",B2:B5), gives TRUE and FALSE, which SUMPRODUCT counts as 0. Multiply instead, or add -- in front: --(D2:D5="East"). |
| The workbook is slow | Whole-column references like A:A make SUMPRODUCT multiply a million rows. Use exact ranges or a table. |
Tips
- Count rows meeting two conditions in any version: =SUMPRODUCT((B2:B50="East")*(C2:C50>1000)).
Questions
How do I calculate a weighted average in Excel?
Divide SUMPRODUCT by the total of the weights: =SUMPRODUCT(B2:B10,C2:C10)/SUM(B2:B10).
Can SUMPRODUCT replace SUMIFS?
Yes, and it can test conditions SUMIFS can't, such as =SUMPRODUCT((MONTH(A2:A50)=3)*C2:C50). SUMIFS is faster and easier to read when it can do the job.
Why does SUMPRODUCT return 0?
Usually because TRUE/FALSE conditions were passed as separate arguments. Multiply them into the other range, or use -- to turn them into 1s and 0s.
Lessons that use SUMPRODUCT
- Lesson 24: SUMPRODUCT (Pro)
Last reviewed October 2026. Every example result on this page is calculated live by the Spreadsheet Practice formula engine.