This page isn't translated yet, so its text is in English. Function names and formulas are shown in your Excel language.
Excel function · Adding up
SUMPRODUCT
Multiplies matching items in ranges, then adds up the results.
What it does
SUMPRODUCT takes two or more ranges of the same shape, multiplies them row by row and adds up the products. The classic use is quantity × price for a whole list in one cell, without a helper column.
Syntax
=SUMPRODUCT(array1, [array2], …)Arguments in square brackets are optional, and … means you can add more of the same. In this Excel language, arguments are separated by ,
Arguments
- array
- A range of numbers. Every range must have the same number of rows and columns. Text and empty cells count as 0.
Example
A shopping basket lists quantities and prices. What does it cost?
| A | B | C | |
|---|---|---|---|
| 1 | Item | Qty | Price |
| 2 | Apples | 4 | 0.50 |
| 3 | Bread | 2 | 2.25 |
| 4 | Milk | 3 | 1.20 |
| 5 | Basket total | 10.10 |
The formula in C5:
=SUMPRODUCT(B2:B4,C2:C4)Result: 10.10
Row by row: 4 × 0.5 = 2, 2 × 2.25 = 4.5 and 3 × 1.2 = 3.6. Together that's 10.1.
Common mistakes
- Ranges of different sizes give a #VALUE! error. Select the same rows in every range.
- Shifting one range by a row, for example
B2:B4withC3:C5. There's no error, but every quantity is multiplied by the wrong price.
In other Excel languages
Excel translates function names, and in some languages the argument separator too. Type the name your Excel shows.
| Excel language | Function name | Separator |
|---|---|---|
| English | SUMPRODUCT | , |
| Deutsch | SUMMENPRODUKT | ; |
| Français | SOMMEPROD | ; |
| Español (España) | SUMAPRODUCTO | ; |
| Español (México) | SUMAPRODUCTO | , |
| 日本語 | SUMPRODUCT | , |
| 한국어 | SUMPRODUCT | , |
| 繁體中文 | SUMPRODUCT | , |
| ไทย | SUMPRODUCT | , |
| ខ្មែរ | SUMPRODUCT | , |
| Tiếng Việt | SUMPRODUCT | ; |