The SUMPRODUCT function in Excel
SUMPRODUCT multiplies the matching cells of two or more ranges and adds up all the products, in one formula and without helper columns.
It is also the classic way to add or count with several conditions, because it can multiply comparisons like =(A2:A5="East"), where TRUE counts as 1 and FALSE as 0.
Syntax
=SUMPRODUCT(array1, [array2], [array3], …)
| Argument | What it means |
|---|---|
| array1 | The first range, or an expression that gives an array of numbers. |
| array2, …optional | More ranges of exactly the same size. Their cells are multiplied with those of array1. |
Examples
1. Total revenue from quantity and price
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Item | Qty | Price | Revenue | |
| 2 | Pen | 10 | 1.5 | 55 | |
| 3 | Notebook | 4 | 3 | ||
| 4 | Folder | 6 | 2 | ||
| 5 | Stapler | 2 | 8 |
10 × 1.5 + 4 × 3 + 6 × 2 + 2 × 8 = 55. No column of line totals is needed.
2. Weighted average
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Part | Score | Weight | Final | |
| 2 | Homework | 90 | 2 | 79 | |
| 3 | Project | 70 | 3 | ||
| 4 | Exam | 80 | 5 |
The sum of score × weight is 790, divided by the total weight 10. A plain AVERAGE would give 80.
3. Revenue of one region
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Region | Units | Price | East revenue | ||
| 2 | East | 10 | 2 | 35 | ||
| 3 | West | 5 | 4 | |||
| 4 | East | 3 | 5 | |||
| 5 | West | 8 | 1 |
The comparison gives 1 for East rows and 0 for the others, so only the East products are added: 20 + 15.
4. Count rows with two conditions
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Region | Units | Price | West, over 5 | ||
| 2 | East | 10 | 2 | 1 | ||
| 3 | West | 5 | 4 | |||
| 4 | East | 3 | 5 | |||
| 5 | West | 8 | 1 |
A row counts only when both comparisons are TRUE, because 1 × 1 = 1 and anything × 0 = 0.
Common mistakes
Ranges of different sizes.
=SUMPRODUCT(B2:B5,C2:C4)returns #VALUE!.Fix: Make every range cover the same number of rows and columns.
Text in a numeric range. Text cells are treated as 0 when the ranges are given as separate arguments, so a typo quietly lowers the total.
Fix: Check the data with COUNT versus COUNTA, or clean the text first.
Using * between ranges and expecting a sum.*
=B2:B5*C2:C5alone returns a list of products.Fix: Wrap the multiplication in SUMPRODUCT to add the products.
Frequently asked questions
When should I use SUMIFS instead?
When every condition is a simple comparison on a column, SUMIFS is easier to read. Use SUMPRODUCT when you need to multiply columns first, or calculate inside a condition.
Does SUMPRODUCT need to be entered as an array formula?
No. It handles arrays by itself in every version of Excel.