Don’t forget to share this post

Google Sheets provides many tools and features that allow you to work with all types of data. In addition to using multiple types of data, you can perform calculations on individual values or arrays of values. Some built-in functions can process and return arrays automatically, but it isn’t always the case. Fortunately, you can wrap your formulas or built-in functions in the ARRAYFORMULA function to ensure it handles arrays appropriately.

In this guide, you will learn about the ARRAYFORMULA function in Google Sheets and what you can do with it. You will learn how to use it to perform calculations with matrices, as well as how to combine it with other Google Sheets built-in functions. You have step-by-step instructions on how to use the ARRAYFORMULA function with the SUMIF, VLOOKUP, and IF functions.

What is the ARRAYFORMULA in Google Sheets?

The ARRAYFORMULA function is very useful as it allows you to perform calculations and use non-array functions with arrays. You can also use them with array functions; however, most array functions will automatically expand to neighboring cells, making this function redundant.

=ARRAYFORMULA(array_formula)

The array_formula parameter can take several forms:

  • A range of cells.
  • A mathematical expression using one or multiple cell ranges of the same size.
  • A function that returns results greater than one cell.

How to Use the Google Sheets ARRAYFORMULA Function?

To manipulate or perform calculations with arrays, wrap your formula or function in the ARRAYFORMULA function. In fact, if you click on your formula in the formula bar and press Ctrl + Shift + Enter (Cmd + Shift + Enter on Mac), the ARRAYFORMULA function will be added automatically.

Follow the steps below to perform matrix multiplication using the ARRAYFORMULA function.

1. Add Formula

In this example, I want to multiply a column containing quantities by a row containing different prices.

Open Google Sheets to the spreadsheet with your data. In an empty cell, type the equal sign followed by the column of cells containing the quantities. Add the multiplication sign and select the row of cells with the prices.

How to Use Google Sheets ARRAYFORMULA Examples Add Formula
How to Use Google Sheets ARRAYFORMULA (+ Examples) - Add Formula

2. Use ARRAYFORMULA Shortcut

Press Ctrl + Shift + Enter on Windows or Cmd + Shift + Enter on Mac. The ARRAYFORMULA function is automatically wrapped around your formula.

How to Use Google Sheets ARRAYFORMULA Examples Use ARRAYFORMULA Function Shortcut
How to Use Google Sheets ARRAYFORMULA (+ Examples) - Use ARRAYFORMULA Function Shortcut

3. Results Array

Press ‘Enter’ to see the resulting array.

How to Use Google Sheets ARRAYFORMULA Examples Results Array
How to Use Google Sheets ARRAYFORMULA (+ Examples) - Results Array

ARRAYFORMULA Examples in Google Sheets

Below, you have three different examples of how to combine the ARRAYFORMULA function with other built-in functions in Google Sheets: SUMIF, VLOOKUP, and IF.

SUMIF & ARRAYFORMULA

For this example, I have a table listing multiple transactions for three different products. Instead of calculating the total for each product, I can use SUMIF to calculate the total per product and ARRAYFORMULA to return an array with the three totals.

  1. 1. Open Google Sheets to the spreadsheet with the data. In this case, a table with multiple transactions for three different products.
How to Use Google Sheets ARRAYFORMULA Examples Open Google Sheets
How to Use Google Sheets ARRAYFORMULA (+ Examples) - Open Google Sheets
  1. 2. In an empty cell, type the SUMIF function and select the range to which you want to apply the criterion. For this example, I will select the cells containing the product names.
How to Use Google Sheets ARRAYFORMULA Examples SUMIF Function Range
How to Use Google Sheets ARRAYFORMULA (+ Examples) - SUMIF Function & Range
  1. 3. After the comma, add the criterion you want to apply to the range. Since I want multiple values to be considered, I will select a column containing the name of each product.
How to Use Google Sheets ARRAYFORMULA Examples Add Criterion
How to Use Google Sheets ARRAYFORMULA (+ Examples) - Add Criterion
  1. 4. Finally, select the cells containing the values you want to sum.
How to Use Google Sheets ARRAYFORMULA Examples Add Sum Range
How to Use Google Sheets ARRAYFORMULA (+ Examples) - Add Sum Range
  1. 5. Add the ARRAYFORMULA function by using the shortcut: Ctrl + Shift + Enter on Windows or Cmd + Shift + Enter on Mac.
How to Use Google Sheets ARRAYFORMULA Examples ARRAYFORMULA Function Shortcut
How to Use Google Sheets ARRAYFORMULA (+ Examples) - ARRAYFORMULA Function Shortcut
  1. 6. Press ‘Enter’ to see the results.
How to Use Google Sheets ARRAYFORMULA Examples Results Array 2
How to Use Google Sheets ARRAYFORMULA (+ Examples) - Results Array
How To Share Only One Tab in Google Sheets
How To Share Only One Tab in Google Sheets

When sharing a Google Sheets spreadsheet Google usually tries to share the entire document. Here’s how to share only one tab instead.

READ MORE

VLOOKUP & ARRAYFORMULA

For this example, I will use a table containing sales amounts per month for four different agents. Using ARRAYFORMULA and VLOOKUP, I can get the results for specific months for all agents at the same time.

  1. 1. In an empty cell, type the VLOOKUP function.
How to Use Google Sheets ARRAYFORMULA Examples VLOOKUP Function
How to Use Google Sheets ARRAYFORMULA (+ Examples) - VLOOKUP Function
  1. 2. Select the cells containing the selected months as the search_key.
How to Use Google Sheets ARRAYFORMULA Examples Add Search Keys
How to Use Google Sheets ARRAYFORMULA (+ Examples) - Add Search Keys
  1. 3. After the comma, add the table as the range.
How to Use Google Sheets ARRAYFORMULA Examples Add Range
How to Use Google Sheets ARRAYFORMULA (+ Examples) - Add Range
  1. 4. For the third parameter, I want more than one column index, so I’ll use array notation to specify the column indices I want: {2,3,4,5}.
How to Use Google Sheets ARRAYFORMULA Examples Add Column Indices
How to Use Google Sheets ARRAYFORMULA (+ Examples) - Add Column Indices
  1. 5. Close the parenthesis and use the ARRAYFORMULA shortcut: press Ctrl + Shift + Enter on Windows or Cmd + Shift + Enter on Mac.
How to Use Google Sheets ARRAYFORMULA Examples ARRAYFORMULA Shortcut
How to Use Google Sheets ARRAYFORMULA (+ Examples) - ARRAYFORMULA Shortcut
  1. 6. Press ‘Enter’ to see the resulting array.
How to Use Google Sheets ARRAYFORMULA Examples Results Array 3
How to Use Google Sheets ARRAYFORMULA (+ Examples) - Results Array
How to Password Protect a Google Sheet
How to Password-Protect a Google Sheet?

If you work with important data in Google Sheets, you probably want an extra layer of protection. Here's how you can password protect a Google Sheet

READ MORE

IF & ARRAYFORMULA

In the example below, I have a table listing sales agents and the corresponding sales amount for the month. If the amount is larger than or equal to $4000, the agent will receive a 5% bonus. I want to calculate and return the bonus amount for sales equal to or larger than $4000, but I want to return a blank if the amount is below $4000.

  1. 1. Open the spreadsheet with your data in Google Sheets.
How to Use Google Sheets ARRAYFORMULA Examples Open Google Sheets 2
How to Use Google Sheets ARRAYFORMULA (+ Examples) - Open Google Sheets
  1. 2. In the first row of the column where you want the results, type the IF function.
How to Use Google Sheets ARRAYFORMULA Examples Add IF Function
How to Use Google Sheets ARRAYFORMULA (+ Examples) - Add IF Function
  1. 3. The condition I want to use is that the cells in the amount column should be above $4000.
How to Use Google Sheets ARRAYFORMULA Examples Add Condition
How to Use Google Sheets ARRAYFORMULA (+ Examples) - Add Condition
  1. 4. After the comma, add the calculation you want to return if the condition is TRUE.
How to Use Google Sheets ARRAYFORMULA Examples Add Calculation if TRUE
How to Use Google Sheets ARRAYFORMULA (+ Examples) - Add Calculation if TRUE
  1. 5. Add another comma, followed by the value you want to return if the condition is FALSE.
How to Use Google Sheets ARRAYFORMULA Examples Add Value if FALSE
How to Use Google Sheets ARRAYFORMULA (+ Examples) - Add Value if FALSE
  1. 6. Close the parenthesis and use the ARRAYFORMULA shortcut to add the function: press Ctrl + Shift + Enter on Windows or Cmd + Shift + Enter on Mac.
How to Use Google Sheets ARRAYFORMULA Examples Add ARRAYFORMULA Function
How to Use Google Sheets ARRAYFORMULA (+ Examples) - Add ARRAYFORMULA Function
  1. 7. Press ‘Enter’ to see the resulting array.
How to Use Google Sheets ARRAYFORMULA Examples Results Array 4
How to Use Google Sheets ARRAYFORMULA (+ Examples) - Results Array

Conclusion

The ARRAYFORMULA function is a very powerful tool. The function allows you to work with arrays as easily as you would with individual cells. You can easily perform matrix operations without having to drag formulas down or to the right. This not only reduces the amount of work you have to do but also the number of formulas that need to be recalculated in your spreadsheet. While many built-in functions can process arrays as input, they can’t always return an array of results. However, you can fix this easily by wrapping your formula or function in the ARRAYFORMULA function.

You now know the ARRAYFORMULA function and how you can use it in Google Sheets. You know how to use ARRAYFORMULA to perform calculations with matrices, and you also know that you can add it after you’ve finished building your formula by using a shortcut. Additionally, you have step-by-step instructions on how to combine the ARRAYFORMULA function with other built-in functions, such as SUMIF, VLOOKUP, and IF.

To learn more about the Google Sheets functions mentioned in this article, check out these guides on:

Hady ElHady
Hady is Content Lead at Layer.

Hady has a passion for tech, marketing, and spreadsheets. Besides his Computer Science degree, he has vast experience in developing, launching, and scaling content marketing processes at SaaS startups.

Originally published Feb 20 2023, Updated Jun 26 2023