The SUMPRODUCT function
Multiplies ranges row by row and adds the results. With a condition in parentheses it works as a flexible SUMIFS.
How to use SUMPRODUCT
SUMPRODUCT multiplies lists against each other row by row and then adds up the results. An invoice is the classic case: quantity times price on every line, and the grand total of all lines, in one formula and without a helper column.
In =SUMPRODUCT(B2:B10,C2:C10) the first range holds the quantities and the second the prices. Every range must have the same size. The function also gives weighted averages: divide by the sum of the weights, as in =SUMPRODUCT(B2:B10,C2:C10)/SUM(B2:B10).
The second use is conditions. A test like (A2:A10="North") gives TRUE or FALSE for every row, and multiplying by 1 or by another range turns that into numbers: =SUMPRODUCT((A2:A10="North")*C2:C10). Several conditions multiplied together work as AND, and added together they work as OR.
For plain conditions SUMIFS is easier to read, and wildcards work there. Reach for SUMPRODUCT when SUMIFS cannot express the rule, such as the month of a date, the length of a text, or OR across columns.
Syntax
SUMPRODUCT(array1, [array2], ...)- array1
- The first range or array.
- array2 (optional)
- More ranges of the same size to multiply with.
Examples
=SUMPRODUCT(1B2:B4,2C2:C4)| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Item | Qty | Price | 450 | |
| 2 | Pen | 4 | 10 | ||
| 3 | Book | 2 | 85 | ||
| 4 | Bag | 1 | 240 |
In plain words: Multiply 1each quantity in B2:B4 by 2its price in C2:C4 and add up the results.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Item | Qty | Price | 95 | |
| 2 | Pen | 4 | 10 | ||
| 3 | Book | 2 | 85 | ||
| 4 | Bag | 1 | 240 |
Common SUMPRODUCT mistakes
SUMPRODUCT returns #VALUE!
The ranges have different sizes, as in
=SUMPRODUCT(B2:B10,C2:C9). Every range must cover the same number of rows and columns.A condition inside SUMPRODUCT gives 0
TRUE and FALSE passed as a separate range are treated as zero. Turn them into 1 and 0 first, with a double minus:
=SUMPRODUCT(--(A2:A10="North"),C2:C10). Multiplying with * does the same.SUMPRODUCT returns #VALUE! when the range includes the header row
Multiplying with * fails on a text cell such as a heading. Start the ranges in the row below the header, as in
=SUMPRODUCT(B2:B10*C2:C10). With commas between the ranges, text is treated as zero.A wildcard like "north" finds nothing
Wildcards only work in functions like SUMIFS, not in an = comparison. Search the text instead:
=SUMPRODUCT(ISNUMBER(SEARCH("north",A2:A10))*C2:C10).
Practice SUMPRODUCT
Read the guide
Questions
What is the difference between SUMIFS and SUMPRODUCT?
SUMIFS adds the cells that meet plain conditions and supports wildcards. SUMPRODUCT multiplies ranges and can test almost anything, such as the month of a date or the length of a text, but it takes a little more typing.
How do I use SUMPRODUCT with multiple conditions?
Multiply the conditions together and finish with the range to add: =SUMPRODUCT((A2:A6="North")*(B2:B6="Shoes")*C2:C6). A row is added only when every condition is true.
Do I need Ctrl+Shift+Enter with SUMPRODUCT?
No. SUMPRODUCT handles arrays by itself, so a normal Enter is enough, in every version of Excel and in Google Sheets.
How do I use OR in SUMPRODUCT?
Add the conditions instead of multiplying them: =SUMPRODUCT(((A2:A6="North")+(A2:A6="South"))*C2:C6) adds the rows that are North or South.