# SUMPRODUCT function in Excel

> SUMPRODUCT multiplies matching items in two or more ranges and adds the results.

Source: https://spreadsheetpractice.com/functions/sumproduct · Updated October 28, 2026 · Formulas and results on this page were run by the Spreadsheet Practice formula engine on November 20, 2026

## Quick answer

- **What it does:** SUMPRODUCT multiplies matching items in two or more ranges and adds the results.
- **Syntax:** `=SUMPRODUCT(array1, [array2], ...)`
- **Most common use:** `=SUMPRODUCT(B2:B5,C2:C5)` returns $6,585 (Total revenue: units × price for every row, added up)
- **Available in:** Every current version
- **Category:** [Math functions](https://spreadsheetpractice.com/functions/category/math)
- **Returns:** The sum of the row-by-row products.
- **Google Sheets:** Works the same in Google Sheets.

## What it does

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.

## 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 was calculated by running the formula on the sample sheet.

| 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 |

### The sample sheet

Every formula above runs on this data:

|  | A | B | C | D | E | F |
|---|---|---|---|---|---|---|
| 1 | Product | Units | Price | Region |  |  |
| 2 | Widget | 40 | $52 | East |  | $6,585 |
| 3 | Gadget | 25 | $89 | West |  | $3,820 |
| 4 | Gizmo | 12 | $145 | East |  | $61.54 |
| 5 | Doohickey | 30 | $18 | West |  |  |

## 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)).

## Related functions

- [SUMIFS](https://spreadsheetpractice.com/functions/sumifs): SUMIFS adds the values where every condition is true: East region widget sales, February revenue, large orders from one customer.
- [PRODUCT](https://spreadsheetpractice.com/functions/product): PRODUCT multiplies all of its arguments together.
- [SUM](https://spreadsheetpractice.com/functions/sum): SUM adds numbers.

## 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.

## Practice

A graded SUMPRODUCT exercise ("Total revenue") is on [the SUMPRODUCT page](https://spreadsheetpractice.com/functions/sumproduct). It is checked instantly, then re-checked on changed data.

Lessons that use SUMPRODUCT:

- [Lesson 35: SUMPRODUCT](https://spreadsheetpractice.com/learn/sumproduct) (Pro)

---

Spreadsheet Practice (https://spreadsheetpractice.com) teaches spreadsheet formulas by practice: every exercise is graded in a live grid and re-checked on changed data.
