Google Sheets Function: ARRAYFORMULA

The ARRAYFORMULA function allows displaying values from an array formula across multiple rows and/or columns, and to use with arrays functions that do not support them.

The examples below should help you to better understand the utility of this very special function and how to use it.

Usage:

=ARRAYFORMULA(array_formula)

Simple usage example

For a simple addition (and without a function), you must enter the following formula =A2+B2:

You then need to copy the formula down:

In this case, there is a formula in each of the 8 cells.

Well, the ARRAYFORMULA function allows displaying the same results but by entering the formula once and in a single cell!

To do this, enter the ARRAYFORMULA function then A2+B2 and replace the cell references with the entire ranges A2:A9+B2:B9.

The formula here is:

=ARRAYFORMULA(A2:A9+B2:B9)

The formula (entered only in cell C2) will then return the result of the addition for each pair of numbers:

Usage example with a function

In the following example, the IF function checks if the quantity has been entered and then calculates the total amount (this example is a copy of that from the IF function):

=IF(B2<>"";B2*C2;"")

To replace this column of formulas with an ARRAYFORMULA, copy the IF function into the ARRAYFORMULA and replace the cell references with references to the cell ranges (for example, instead of B2, enter the range B2 to B7).

The formula then becomes:

=ARRAYFORMULA(IF(B2:B7<>"";B2:B7*C2:C7;""))

The formula (entered only in cell D2) will therefore return the result for the 6 lines: