How To Use Array Formula Google Sheets

To simplify how ARRAYFORMULA works, you must add it before using arrays in functions that normally don’t use arrays as arguments, like SUM.

If that’s a little confusing, don’t worry. This easy array formula Google Sheets tutorial will teach you how to use the ARRAYFORMULA function with several examples from basic to intermediate. Read on to learn more.

Array Formula Google Sheets Syntax

The ARRAYFORMULA Google Sheets function uses a simple formula requiring only one argument. Before I show you some examples of the function, here is the ARRAYFORMULA Google Sheets function syntax:

=ARRAYFORMULA(formula)

The formula parameter can be an expression or a range. It can use a single or multiple ranges of the same size. It can also be a cell range, a mathematical expression, multiple ranges, or a formula that returns a result that can be larger than one cell.

One handy thing I like about Google Sheets is that you can add ARRAYFOMULA to an existing function. To do this, click on the cell containing the formula and press the Ctrl+ Shift+ Enter keyboard shortcut.

Although the concept sounded complex initially, using it made me realize that the ARRAYFORMULA is much simpler to use than you think.

How To Use the ARRAYFORMULA in Google Sheets

Now that we know how the ARRAYFORMULA function works let me explain how you can use the formula in your Google Sheets. Here is how to use the array formula in Google Sheets:

Simple ARRAYFORMULA

Here is a simple example of the function in action. In this example, I have a table with the names of products, their quantity, and price.

To find the total, I will use the following formula:

=Quantity*Cost

Additionally, I will use the Google Sheets Autofill feature to fill in the formula in the Total column.

However, this can be tedious if I have a column with thousands of values. Instead, I can use the function to fill in the entire column by typing in the formula in one cell.

Here is how I would do this:

1. I open the Google Sheets where I want to enter the ARRAYFORMULA function.
2. There, I click on the cell where I want to enter the formula.
   • Here, I make sure I choose the top-left cell in the table. This will fill the entire column with the values using the example above.

3. I start by entering the initial part of the formula, which is:

=ARRAYFORMULA(

4. Using the multiplication formula I described earlier, I want to show the value in the Total column.
   • To do this, I will multiply the columns with each other (i.e., select the ranges). The formula in this case is:

 C4:C6*D4:D6

5. Finally, I add a Closing Bracket “)” and press “Enter” to execute the formula.

Depending on the number of cells in the column, Sheets may take a minute to calculate the values. One thing to note is that the formula will only work if the sizes of the array match. In this case, both columns have three cells, meaning the columns can be multiplied.

Related: How To Subtract in Google Sheets

Google Sheets ARRAYFORMULA with SUM Function

Taking the next step, I now want to nest a SUM formula into an ARRAYFORMULA to add all the values I calculated using the function in the previous example.

Here is how I do this in Google Sheets:

1. In the spreadsheet, I will click on the cell where I want to enter the formula.
   • In this example, there will only be one output, so the placement of the formula doesn’t matter.

2. Now, I enter the initial part of the formula, which is:

=ARRAYFORMULA(

3. To nest the SUM function in the ARRAYFORMULA, I will enter the SUM function as an argument. The entire formula looks like this:

=ARRAYFORMULA(SUM(

4. Now, I’m going to enter the formula in the SUM function. The formula in this case is:

C4:C6*D4:D6

5. Finally, I add a Closing Bracket “)” and press “Enter” to execute the formula.

This example shows that the ARRAYFORMULA function can be used cleverly to perform other array-related functions in your spreadsheet.

Google Sheets ARRAYFORMULA with SUMIF Function

Another great way to use the ARRAYFORMULA function is with aggregation functions like SUMIF. This allows me to quickly add values in a range that fulfill specific criteria.

In this example, I have a dataset with the products’ names and their sales numbers. I want to combine all the sales numbers for a particular product in another place in the spreadsheet. Therefore, I will use a SUMIF array formula to do this.

Here is the syntax of the SUMIF formula:

=SUMIF(range, condition, sum_range)

The formula needs three parameters to work.

• The first parameter is the cell or cell range to check if it meets a specific condition
• The second parameter is the criteria to check for
• The third parameter defines the cell range containing the values you want to add

Here is how to use ARRAYFORMULA with SUMIF in Google Sheets:

1. In the spreadsheet where I want to use the function, I will click on the cell and enter the initial part of the formula, which is:

=ARRAYFORMULA(

2. To nest the SUMIF function in the ARRAYFORMULA, I will enter the SUM function as an argument. The entire formula looks like this:

=ARRAYFORMULA(SUMIF(

3. Now, I will enter the parameters for the SUMIF formula, which are:

B4:B12, E4:E6, C4:C12

• Here, the first parameter is the cell range with the products, the second parameter contains the criteria, and the third contains the amount of products I want to add.

4. Finally, I add a Closing Bracket “)” and press “Enter” to execute the formula.

This will find the values corresponding to the item and add them. The sum will then be shown beside the product name.

Using ARRAYFORMULA means I can use SUMIF for multiple products without needing the formula to recalculate multiple times, making my spreadsheet much faster.

Related: Google Sheets Fill Down: Easy 2024 Guide with GIFs

Google Sheets ARRAYFORMULA to Combine Columns

One handy way to use the ARRAYFORMULA is to use it to combine columns. In this example, I have two columns with the first and last names of people. If I want to combine them into one column with the full names, I can use the following formula:

=A1 & " " & B1

You can use the autofill feature to fill the formula in the entire column. Even though this can work if you have a few cells in the column, Sheets will also have to calculate the formula for each instance, meaning it can become very slow if there are thousands of cells.

Using the ARRAYFORMULA function in this example can save you from unnecessary calculations.

Here is how to use ARRAYFORMULA to combine columns:

1. In the spreadsheet where I want to combine columns, I will click on the cell and enter the initial part of the formula, which is:

=ARRAYFORMULA(

2. Next, I’m going to insert the formula I showed above.
   • In this example, the first names are in the cell range B4:B9, while the last names are in the C4:C9 cell range.
   • To join them, I will use the Ampersand (&) symbol.
   • To ensure a space between the names, I will use two Quotation Marks ( “ ) with a space in between. The formula will be:

B4:B9 & " " & C4:C9

3. Finally, I’m going to add a Closing Bracket “)” and press “Enter” to execute the formula.

Google Sheets ARRAYFORMULA with IF Function

Let’s take a look at how we can use the IF function with ARRAYFORMULA.

In this example, I will use sample data that includes students’ names and marks. I want to determine if their marks are over the passing threshold. If I want to manually autofill the IF function into each cell, it will lead to a substantial number of instances where the same formula is executed. This can potentially lead to a significant slowdown in my Google Sheets performance.

Here is how I use ARRAYFORMULA IF Google Sheets:

5. In the spreadsheet, I will click on the cell and enter the initial part of the formula, which is:

=ARRAYFORMULA(

6. To nest the IF function into the ARRAYFORMULA, I will enter the SUM function as an argument. The entire formula looks like this:

=ARRAYFORMULA(IF(

7. Now, I’m going to enter the parameters for the IF formula, which are:

C4:C7<50, "No", "Yes"

• Here, the first parameter is the cell range, and the second and third parameters define what strings to display if the values are true or false, respectively.

8. Finally, I add a Closing Bracket “)” and press “Enter” to execute the formula.

Google Sheets will apply the IF formula to each of the cells defined in the formula, in this case, C4 to C7. Based on the student’s marks, will the formula display “Yes” or “No” under the Pass? column.

Related: How to VLOOKUP Multiple Columns in Google Sheets

What Is the Google Sheets Array Formula?

The ARRAYFORMULA function in Google Sheets is a handy formula that can output a cell range instead of singular values. The formula may contain nested non-array formulas, meaning the possibilities are endless.

What sets this function apart is that it can process data in a way that regular formulas can’t. For instance, I can input a formula into a cell or a small cell range, and the formula can extend its calculation to cover a larger range of cells. This means I don’t have to copy and paste the formulas or use the autofill feature.

The ARRAYFORMULA function is versatile, especially when dealing with large datasets. They help me automate tasks that are impractical to do manually, ensuring accuracy and consistency in calculations.

Whether it’s complex data analysis or data consolidation, using ARRAYFORMULA makes my spreadsheets more efficient and organized.

Why Use an Array Formula in Google Sheets?

Google Sheets Array Formula is great! I’ve started incorporating it into many tasks I do in Google Sheets. Here are some of the benefits of using ARRAYFORMULA in Sheets:

Multiple Cell Calculation

ARRAYFORMULA allows me to perform calculations on multiple cells or an entire range of cells in a single formula. This saves me a significant amount of time compared to writing individual formulas for each cell.

Simplifies Complex Calculations

The ARRAYFORMULA is particularly useful for handling complex calculations. This helps me write complex formulas into a single cell, which keeps my spreadsheet more organized.

Avoids Repetitive Tasks

The ARRAYFORMULA helps automate repetitive tasks that would otherwise need manual entry or dragging the formulas down a column. This reduces the risk of errors and ensures consistency in my calculations.

Dynamic Changes

The function is dynamic, meaning it automatically changes the output based on the input. This means that when I make any changes to my spreadsheet, I don’t have to update the data in the other cells, as it helps maintain the accuracy of my calculations.

Encapsulated Formulas

I’ve found that using array formulas can make my spreadsheet formulas more concise and easier to read. Instead of having many separate formulas scattered across the sheet, I can have one array formula that encapsulates the entire calculation.

Although Google Sheets has a “Suggested Autofill” feature that allows me to apply the formula to the rest of the cells in a row or column, it doesn’t allow as much flexibility as the ARRAYFORMULA.

For example, suppose I alter the values in my spreadsheet. In that case, there is a risk that the suggested autofill won’t be able to update all the cell data properly, which can leave me with inaccurate, out-of-date information.

Related: How To Remove Duplicates in Google Sheets

Frequently Asked Questions

What Is Array Formula in Google Sheets?

The ARRAYFORMULA function in Google Sheets allows me to apply a formula to an entire cell range simultaneously. I don’t have to manually enter the formula into each cell or use the suggested autofill feature. This helps me save time and maintain accuracy in my spreadsheet.

What Is the Difference Between Array Formulas and Non-Array Functions

There are several differences between array formulas and normal formulas. A normal formula typically returns a single value as a result. However, array formulas can perform multiple calculations and return multiple results.

Non-array functions are best for simple calculations, but array formulas can require a different syntax and are used for complex calculations, handling large data, and performing operations on multiple rows and columns.

Why Are My Array Formulas Not Updating Automatically in Google Sheets?

Make sure that the Automatic Calculations function is enabled in Sheets.

To do this:

1. Click on “File” > “Spreadsheet Settings” > “Calculation”
2. Change the setting to “On Change” and “Every Hour”
3. Recalculate the spreadsheet using the Ctrl+ Alt+ F9 keyboard shortcut key.
4. Ensure there are no circular references and your spreadsheet hasn’t exceeded the recalculation limit.

When to Use Array Formulas in Google Sheets?

The ARRAYFORMULA in Google Sheets is particularly useful when you want to streamline calculations or apply a formula to an entire cell range.

It works best when you streamline mathematical calculations and apply a function across an entire dataset, simplifying complex spreadsheets in Google Sheets. It also ensures that your calculations stay current as changes are made to the data.

How Do I Apply an Array Function to an Entire Column?

When entering the array arguments in the formula, you use the entire column value. For example, if you used column E, you would use E: E. For column H, it would be H: H.

Why Do I Need to Be Careful with Array Sizes?

If the size of the arrays used as arguments in an ARRAYFORMULA calculation do not match, the formula will fail.

Can ARRAYFORMULAS Be Exported?

Unfortunately, Google Sheets does not currently support exporting array formulas.

Wrapping Up

If you’ve followed this ARRAY FORMULA Google Sheets guide closely, you should now have the tools to use the ARRAYFORMULA with other functions that we haven’t mentioned as well. You can also upskill your Google Sheets knowledge with this helpful Google Workspace course, where you’ll cover tips and tricks, resources, and helpful information to work faster and smarter on Google Workspace.

Related:

• The Best Google Forms and Sheets Course
• How To Use the DATE Google Sheets Function [Easy Examples]
• Google Sheets vs Excel: Which Wins?