ARRAYFORMULA Function in Google sheet

ARRAYFORMULA Function is one of the most tricky but useful function you will come across in Google Sheets. Though is not commonly used, it’s highly recommended to do so.

In this article we will take an attempt to de-mystify the function and share with you relevant real-world usage of the function.

Purpose of ARRAYFUNCTION

ARRAYFUNCTION lets us apply output to multiple rows/columns by operating in a single cell. It also enables us to use non-array functions with arrays.

Below is a very basic example of using ARRAYFUNCTION to calculate sum of A2:A6 & B2:B6 to C2:C6. All of this by simply plugging the below formula into the cell C2.

=ArrayFormula(A2:A6 + B2:B6)
Sum using Array Formula - Google Sheets

This minor enhancement saved the entire effort of copy-pasting the columns across all the rows in column C.

Syntax and Parameter Definition

=ARRAYFORMULA(array_formula)
  1. array_formula: A range, mathematical expression using one cell range or multiple ranges of the same size, or a function that returns a result greater than one cell.

Note:

  1. Multiple cell ranges/arrays can be used in arrayformula but all should be of same length
  2. To convert any normal formula to ARRAYFORMULA, simple press CTRL+SHIFT+ENTER
  3. Non-array functions like IF, TEXT etc can used with array inputs while applying ARRAYFORMULA

Expected Output

ARRAYFORMULA Returns the output of entered formula to across the entire cell range/array. Let’s try to understand this by a simple example.

The Formula A2 + B2 simply returns the sum total of A2 & B2 in C2.

Simple Sum in Google Sheet

Now if we were to replace A2 with A2:A6 and B2 with B2 and B2:B6, and enclose the formula by ARRAYFORMULA Function we get the below results.

Sum using Arrayformula in google sheets

The end result is the formula getting applied across C2:C6.

As a minor modification to the above we can also let the input array range being open-ended like A2:A instead of A2:A6. This free’s us of the dependency of having bounded cell range and the formula gets applied across the column.

Sum two columns

More Examples

Lets have a look at more ways to use ARRAYFORMULA in Google sheet.

IF and ARRAYFORMULA in Google Sheet

In the below example we have used an IF Formula within array Function, which under normal circumstances is impossible. But not when we have ARRAYFORMULA.

=ARRAYFORMULA(if(C2:C6 >10, ">10","<=10"))
IF and Arrayformula

Embedding C2:C6 with the IF Function and using it with ARRAYFORMULA helps us apply IF Function to all cells C2:C6 in one go.

SUM and ARRAYFORMULA in Google Sheet

From the previous examples we now know that Arrayformula returns output as an array. Now it’s not always necessary that we need to display the entire output. If we supply the output to another function like SUM/COUNT then we get a singular output possible.

= SUM(ARRAYFORMULA(A2:A6 * B2:B6))

Embedding C2:C6 with the IF Function and using it with ARRAYFORMULA helps us apply IF Function to all cells C2:C6 in one go.

Multiplication using Arrayformula

Embedding C2:C6 with the IF Function and using it with ARRAYFORMULA helps us apply IF Function to all cells C2:C6 in one go.

ARRAYFORMULA Applied Horizontally Across Columns

When we supply horizontal cell ranges like below, the array output from ARRAYFROMULA Function.

=ARRAYFORMULA(B1:F1 * B2:F2)

A small video tutorial if it helps.

That’s it on this topic. Keep browsing SheetsInfo for more such useful information 🙂

How useful was this post?

Click on a star to rate it!

Average rating 0 / 5. Vote count: 0

No votes so far! Be the first to rate this post.

Rishav

I love Google Sheets and heavily rely upon it on a day to day basis. SheetsInfo is my attempt to share my learning with all of you. Hope you find the articles easy to read and simple to understand.

Leave a Reply