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.
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.
3. Results Array
Press ‘Enter’ to see the resulting 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. Open Google Sheets to the spreadsheet with the data. In this case, a table with multiple transactions for three different products.
- 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.
- 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.
- 4. Finally, select the cells containing the values you want to sum.
- 5. Add the ARRAYFORMULA function by using the shortcut: Ctrl + Shift + Enter on Windows or Cmd + Shift + Enter on Mac.
- 6. Press ‘Enter’ to see the results.
When sharing a Google Sheets spreadsheet Google usually tries to share the entire document. Here’s how to share only one tab instead.
READ MOREVLOOKUP & 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. In an empty cell, type the VLOOKUP function.
- 2. Select the cells containing the selected months as the search_key.
- 3. After the comma, add the table as the range.
- 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}.
- 5. Close the parenthesis and use the ARRAYFORMULA shortcut: press Ctrl + Shift + Enter on Windows or Cmd + Shift + Enter on Mac.
- 6. Press ‘Enter’ to see the resulting array.
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 MOREIF & 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. Open the spreadsheet with your data in Google Sheets.
- 2. In the first row of the column where you want the results, type the IF function.
- 3. The condition I want to use is that the cells in the amount column should be above $4000.
- 4. After the comma, add the calculation you want to return if the condition is TRUE.
- 5. Add another comma, followed by the value you want to return if the condition is FALSE.
- 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.
- 7. Press ‘Enter’ to see the resulting 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:
- SUMIF & SUMIFS in Google Sheets: Formula & Examples
- VLOOKUP with IMPORTRANGE in Google Sheets
- How to VLOOKUP From Another Google Sheet or Workbook (+ Examples)
- IF Statement in Google Sheets: Formula & Examples
- 23 Must-Know Google Sheets Formulas