How to Use the ArrayFormula Function in Google Sheets

Mastering how to use the ArrayFormula Function in Google Sheets will open up a new dimension of analysis in your spreadsheets. With the ArrayFormula function, you can up your game in Google Sheets in a number of ways, including by:

• Eliminating tedious manual work 
• Streamlining and decluttering your spreadsheets 
• Simplifying your logic and formulae

These benefits only scratch the surface, of course. The following ultimate guide covers how the Google Sheets ArrayFormula function works, what its benefits are, and examples of how to use it. 

What is the ArrayFormula function in Google Sheets?

The ArrayFormula function in Google Sheets applies a single formula to every cell in your pre-defined data range. The function turns your original formula into an array, allowing you to write only one formula and use it across multiple rows. 

The general gist is straightforward: input the formula in the first cell, define the array’s size, and the function will apply the formula to each cell.  The ArrayFormula function outputs the values from cell ranges into multiple columns and rows. 

Unlike standard formulas, the function is expandable, so it iterates on new data instantly. You can also use ArrayFormula in conjunction with other functions, such as VLOOKUP, FILTER, IF, or SUMIF.  

Benefits of Using the Google Sheets ArrayFormula Function

The ArrayFormula function in Google Sheets offers many benefits, including the following: 

• Batch size calculations: The ArrayFormula function lets you use a single formula for your entire dataset, no matter its size. With ArrayFormula’s batch size calculations, you can reduce performance issues caused by too many formulas and calculations in your spreadsheet. 

• Flexibility and expandability: A modification to any cells in the data range will automatically alter the ArrayFormula’s calculation. You can easily edit cell values, and values relying on these cells.

• Dynamic functionality. An ArrayFormula function applies to any new row added within your cell ranges, so you won’t need to input another function manually. This saves you time and helps avoid errors.

In our experience with our own customers, we’ve found that ArrayFormula is helpful for spreadsheets that contain accurate and up-to-date data, such as weekly sales reports or sales pipeline dashboards in Google Sheets.  

ArrayFormula function syntax

The ArrayFormula function’s syntax is as follows:

=ARRAYFORMULA(array_formula)

The array_formula parameter can be:

• A range
• A mathematical expression that uses ranges of the same size
• A function that returns a result greater than a single cell

You can add an ArrayFormula to existing functions. Press Cmd/Ctrl + Shift + Enter to add an ArrayFormula around your function in Google Sheets. 

How to use the Array Formula function in Google Sheets

Learn how to use the ArrayFormula function with the examples below, and watch our video above for a full walkthrough.

Basic ArrayFormula

Consider this dataset containing the number of subscribers and costs for four different products. 

The basic method of calculating the total cost of the four products is to include a formula in column D that multiplies columns B and C.  

Then insert a SUM function into cell D6. 

The ArrayFormula lets you skip these steps by combining this entire process into a single arrayed formula. 

For this example, use this ArrayFormula:

=ArrayFormula(SUM(B2:B5 * C2:C5))

You will include two ranges (or two arrays) in your formula:

= B2:B5 * C2:C5

However, if you just insert this array-based formula as is, you will see an error: 

To resolve this, you’ll need to tell Google Sheets you want the formula to be an ArrayFormula. 

=ArrayFormula(B2:B5 * C2:C5)

The formula now works. 

Note: Array formulas only work if the two array sizes match. The sample dataset contains four numbers, allowing each row to be multiplied. 

The last step is to include the SUM function to add the four values returned by the ArrayFormula.

=ArrayFormula(SUM(B2:B5 * C2:C5)) 

The final result is shown here:

Additionally, you can use the SUMPRODUCT formula for the calculation, taking the array values as inputs. 

=SUMPRODUCT(B2:B5,C2:C5)

Use GPT to Generate Array Formulas

You can use Coefficient’s free Formula Builder to automatically create the array formulas in this first example. To use Formula Builder, you need to install Coefficient by following along with the prompts. The install process takes less than a minute.

Once the installation is finished, return to Extensions on the Google Sheets menu. Coefficient will be available as an add-on.

Now launch the app. Coefficient will run on the sidebar of your Google Sheet. Select GPT Copilot on the Coefficient sidebar.

Then click Formula Builder.

Type a description of a formula into the text box.

Let’s type: Type a description of a formula into the text box. For this example, type: Multiply the values in column B from row 2 to row 5 by the values in column C from row 2 to row 5.

Then press ‘Build’. Formula Builder will automatically generate the formula for you.

Now, simply copy-paste your formula in your desired cell.

Advanced Functions

ArrayFormula with IF function

You can use ranges (arrays) with non-array functions. You can do this by designating the IF formula as an ArrayFormula. 

Suppose you have this dataset:

And you use an IF statement to check if a value in column A is more than $2,000. 

=IF(A2>2000,”Yes”,”No”)

You copy the formula down to run the test for each row. 

But there’s another way to do this: use an ArrayFormula at the top of the column. Then run the IF statement through the rest of the rows. 

Create a single Array Formula that includes the values in rows 2 to 10:

=ArrayFormula(IF(A2:A10>2000,”Yes”,”No”))

The single formula in cell 2 creates an output array, filling rows 2 to 10 with Yes or No answers. 

That’s the power of combining ArrayFormula with the IF function. 

ArrayFormula with SUMIF and COUNTIF

You can deploy Array Formulas with aggregation functions, such as SUMIF and COUNTIF, to quickly add values within a range. Let’s say your dataset contains the sales of each item for every location of your business. 

Now calculate the total sales for each item and show the results in cells F3:F5. 

If you use a simple SUMIF function, you’ll need to apply the formula three times (one for each item), since the function only lets you specify a single cell at a time.  

However, ArrayFormula lets you specify a range of cells:

=ARRAYFORMULA(SUMIF(B2:B10,E3:E5,C2:C10))

The results will look like this:

Specify the range of cells E3:E5 in the formula to generate the results for the three items at once. 

To count the number of entries for each item, combine the COUNTIF function with ArrayFormula like you did with SUMIF. 

However, if you combine COUNTIFS or SUMIFS with an Array Formula, you’ll still only get one result. But what if you want multiple results?

The key is to refine your formula within the SUMIF function. To use multiple conditions within a SUMIF function, you must harness an ArrayFormula and ampersand (&) operators. 

For instance, if you want to find out the total sales for each item in the UK, your formula would read:

=ARRAYFORMULA(SUMIF(A2:A10&B2:B10,F3:F5&E3:E5,C2:C10))

But these two conditions must remain true:

• The item name within column B must match the item name of the corresponding cell in column E. 
• The location in column A should match the total values in column F.

By adding ArrayFormula and (&) operators, you can enable the SUMIF function to expand its results to the entire column. This allows you to check for more than one criterion. 

Deploy ArrayFormulas to concatenate text

ArrayFormula enables you to combine columns and concatenate the text within them. Let’s use a dataset with a list of first and last names. 

Use a concatenation operator in your ArrayFormula to combine the two columns, like this:

=ARRAYFORMULA(A2:A7&” “&B2:B7)

You should see the first and last names combined in one column, with a space in between.

You can also use the concatenation operator in your ArrayFormula to quickly merge text with dates, numbers, and special characters. 

ArrayFormula Function: An Indispensable Google Sheets Tool

The ArrayFormula function is one of the most versatile tools in Google Sheets. The function can combine and expand multiple functions in Google Sheets simultaneously, making your analysis more incisive, and your spreadsheets more performant. 

Want to implement the ArrayFormula function in a live Google Sheet? Now, with Coefficient, you can unlock the full power of ArrayFormula by leveraging real-time data from your company systems in Google Sheets.

Get started with Coefficient for free right now!