How to Use the SUMPRODUCT Function in Google Sheets

By Tomosuke Hasegawa · Updated Oct 29, 2026 · Intermediate · 6 min read · includes 2 free exercises

In short: SUMPRODUCT multiplies matching entries in two or more ranges and adds up the results. The formula is =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.

ABC
1ItemQtyPrice
2Pen102
3Book312
4Bag225
5Cap58
=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

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.

Hint: pass B2:B5 and C2:C5.

Exercise 2: Array constants

Write a formula using SUMPRODUCT on the array constants {1,2,3,4} and {5,6,7,8}.

Hint: curly braces and commas inside each array.

What to learn next

Syntax and rules in this guide were checked against Google's SUMPRODUCT help page. See all guides.