How to Use ARRAYFORMULA in Google Sheets

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

In short: ARRAYFORMULA spreads the result of a formula across many rows and columns, and lets functions that normally take one value work on a whole range. The formula is =ARRAYFORMULA(array_formula).

What ARRAYFORMULA does

Normally you write a formula in one cell and drag it down. With ARRAYFORMULA you write it once and the results fill the cells below. The formula is a single unit: it cannot be left half-copied, and new rows do not need a fill-down if the range covers them.

ARRAYFORMULA syntax

=ARRAYFORMULA(array_formula)

According to Google's help page, array_formula can be a range, a math expression that uses one or more ranges of the same size, or a function that returns a result larger than one cell.

A step-by-step example

Here is an order table in cells A1:C4. You want a line total (quantity times price) for every row.

ABC
1ItemQtyPrice
2Pen31.5
3Book24
4Cap52

Without ARRAYFORMULA you would write =B2*C2 and copy it down. With it, one formula does the whole column:

=ARRAYFORMULA(B2:B4*C2:C4)

This returns 4.5, 8 and 10, one per row, in the three cells starting at the formula. Here is why:

  1. B2:B4*C2:C4 multiplies the two ranges row by row, because they are the same size.
  2. ARRAYFORMULA tells Sheets to show every result instead of only the first one.

The cells where the results land must be empty, or Sheets shows an error instead of overwriting them.

Use it inside another function

You can also use array math to collapse a whole range into one number. Google's own example sums only the values above 5:

=ARRAYFORMULA(SUM(IF(A1:A10>5, A1:A10, 0)))

For the order table, this gives the total of all line totals in one cell:

=ARRAYFORMULA(SUM(B2:B4*C2:C4))

This returns 22.5 (4.5 + 8 + 10).

Common ARRAYFORMULA mistakes

Try it yourself

Use the order table above (A1:C4). Type your formula into the box, starting with =, then press Check.

Exercise 1: One formula, whole column

Write one formula that multiplies Qty (B2:B4) by Price (C2:C4) for every row and spreads the results down.

Hint: wrap B2:B4*C2:C4 in ARRAYFORMULA.

Exercise 2: Total in one cell

Write a single formula that returns the sum of all the line totals (Qty times Price) in one cell, using ARRAYFORMULA.

Hint: put SUM around the multiplication, inside ARRAYFORMULA.

What to learn next

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