The SUMPRODUCT function
Multiplies ranges row by row and adds the results. With a condition in parentheses it works as a flexible SUMIFS.
Syntax
SUMPRODUCT(array1, [array2], ...)- array1
- The first range or array.
- array2 (optional)
- More ranges of the same size to multiply with.
Examples
E1
=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.
E1=SUMPRODUCT((B2:B4>1)*C2:C4)
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Item | Qty | Price | 95 | |
| 2 | Pen | 4 | 10 | ||
| 3 | Book | 2 | 85 | ||
| 4 | Bag | 1 | 240 |