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.
Table of Contents
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)
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)- 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:
- Multiple cell ranges/arrays can be used in arrayformula but all should be of same length
- To convert any normal formula to ARRAYFORMULA, simple press CTRL+SHIFT+ENTER
- 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.
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.
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.
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"))
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.
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 🙂
