How to Use the SUMPRODUCT Function in Google Sheets
=SUMPRODUCT(array1, [array2, ...]). It is the one-cell way to total quantity times price.
What SUMPRODUCT does
To get an order total you would normally add a helper column of quantity times price, then sum it. SUMPRODUCT does both steps in one formula: it multiplies the first entries of each range together, the second entries together, and so on, then adds all the products.
SUMPRODUCT syntax
=SUMPRODUCT(array1, [array2, ...])
Per Google's help page, each entry of array1 is multiplied by the matching entry of array2. If you leave out array2, it defaults to an array of 1s the same length as array1. The ranges should be the same size.
A step-by-step example
Here are four items with a quantity and a unit price in cells A1:C5.
| A | B | C | |
|---|---|---|---|
| 1 | Item | Qty | Price |
| 2 | Pen | 10 | 2 |
| 3 | Book | 3 | 12 |
| 4 | Bag | 2 | 25 |
| 5 | Cap | 5 | 8 |
=SUMPRODUCT(B2:B5, C2:C5)
This returns 146. Here is why: 10 × 2 = 20, 3 × 12 = 36, 2 × 25 = 50 and 5 × 8 = 40, and 20 + 36 + 50 + 40 = 146. No helper column was needed.
Array constants
You can also type the arrays directly. Google's page uses SUMPRODUCT({1,2,3,4},{5,6,7,8}) as an example. Multiplying pair by pair gives 5, 12, 21 and 32, so the result is 70.
SUMPRODUCT or SUMIFS?
SUMPRODUCT multiplies columns and adds the products, so use it for totals like quantity times price. SUMIFS adds one column but only for rows that meet conditions. They answer different questions, and you can use both in the same sheet.
Common mistakes
- Ranges of different sizes. Keep each range the same length, such as B2:B5 and C2:C5.
- Including the header row. Text headers are not numbers, so start the ranges below them.
- Using it when SUM is enough. A single range with no multiplication needs only SUM.
Try it yourself
Use the table above for exercise 1. Type your formula into the box, starting with =, then press Check.
Exercise 1: Order total
Write a formula that returns the order total: quantity times price for every row, added together.
Exercise 2: Array constants
Write a formula using SUMPRODUCT on the array constants {1,2,3,4} and {5,6,7,8}.
What to learn next
- SUMIFS: add up values that meet several conditions.
- ROUND: round numbers to any number of places.
- AVERAGE: find the mean of a range.
Syntax and rules in this guide were checked against Google's SUMPRODUCT help page. See all guides.