fbpx

Win $2,000 by Conquering the Ultimate Google Sheets Escape Room. Play the Escape Room

Blog /

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. 

arrayformula syntax google sheets

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.  

arrayformula google sheets

Then insert a SUM function into cell D6. 

sum array spreadsheet

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: 

error arrayformula google sheets

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

=ArrayFormula(B2:B5 * C2:C5)

arrayformula formula spreadsheet

The formula now works. 

Google Sheets formula array

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. 

Over 60K pros building reports use Coefficient to automate business systems data into their Google Sheets
"What an amazing app! Coefficient makes integrating some of the most complex systems unbelievably simple! Set up your integrations in less than a minute."
Yonatan Schvimer

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

SUM function arrayformula

The final result is shown here:

SUMPRODUCT formula google sheets

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

=SUMPRODUCT(B2:B5,C2:C5)

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:

if function arrayformula

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

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

if statement arrayformula spreadsheet

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

copy formula if function

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. 

arrayformula if statement google sheets

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

google-sheets-escape-room

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. 

sumif countif arrayformula

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

arrayformula sales formula

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))

array sumif google sheets

The results will look like this:

data range array formula

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?

multiple results arrayformula google sheets

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))

ampersand operator google sheets

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. 

arrayformula concatenate text

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

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

concatenation operator google sheets

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

combine text array google sheets

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!

Wait, there's more!

Connect any system to Google Sheets in just seconds.

Get Started Free
60,000+ users on
Google Marketplace
Trusted by thousands
of companies