At a fundamental level, it takes the values from a cell range and outputs them into multiple rows and columns. This means it lets you use arrays in formulas that usually don\u2019t accept them, <\/b>including SUMIF, VLOOKUP, or FILTER.<\/p>\n
This guide covers how ARRAYFORMULA works, why you’d use it, nine array formula examples (including IF, SUMIF, VLOOKUP, and FILTER) and the errors you’re most likely to hit.<\/p>\n
[\/et_pb_text][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_text][et_pb_text _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
What is the ARRAYFORMULA?<\/h2>\n
The ARRAYFORMULA is an expandable array function. This means that you only need to insert the function into one cell, and then the function is automatically applied to the subsequent rows throughout the length of a whole column.<\/p>\n
As a result, you can return multiple values from a range of cells with just one ARRAYFORMULA, rather than returning a single value from a non-array formula.<\/p>\n
[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2022\/06\/ARRAY-\u2013-1.png” alt=”Google Sheets ARRAYFORMULA \u2014 a separate formula in each Total row compared with one ARRAYFORMULA for the whole column” title_text=”A separate formula in each Total row compared with one ARRAYFORMULA for the whole column” align=”center” _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
Let\u2019s take a look at the syntax to understand how it works and how to combine it with other functions.<\/p>\n
[\/et_pb_text][et_pb_text _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
Syntax<\/h3>\n
The syntax for the ARRAYFORMULA is as follows:<\/p>\n
[\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.16″ border_width_left=”4px” global_colors_info=”{}” theme_builder_area=”post_content”]=ARRAYFORMULA(array_formula)[\/et_pb_text][et_pb_text _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
array_formula<\/b> \u2013 this parameter can be:<\/p>\n [\/et_pb_text][et_pb_text _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n You can also add the ARRAYFORMULA to an existing non-array function in your spreadsheet too. Simply press the keyboard shortcut Ctrl\/Cmd + Shift + Enter, and Google Sheets will automatically wrap ARRAYFORMULA() around your formula.<\/p>\n As you can see, the ARRAYFORMULA syntax is simpler than expected. Let\u2019s explore more benefits of this function.<\/p>\n [\/et_pb_text][et_pb_text _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n As well as saving time and manual effort by offering an alternative to manual calculations, the ARRAYFORMULA also offers other benefits.<\/p>\n Let\u2019s take a look at just a few:<\/p>\n Although Google Sheets may offer \u201cSuggested autofill\u201d to apply the function to the rest of your rows, the autofill will not be able to provide the same benefits as above. For example, If you alter your values, there is the risk that the autofill will not be able to update all of your cell data correctly. This would leave you with out-of-date, inaccurate information.<\/p>\n Now that you understand the many benefits of using the ARRAYFORMULA in mass calculations, Let\u2019s explore how to use the Google Sheets ARRAYFORMULA in the following examples.<\/p>\n [\/et_pb_text][et_pb_text _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n Let\u2019s say I\u2019m a teacher and have a dataset with a list of students and their notes on different projects. I want to calculate the average score for each student across all projects.<\/p>\n Usually, I would add up the cell values individually, as shown below:<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2022\/06\/ARRAY-\u2013-2.png” alt=”Google Sheets ARRAYFORMULA \u2014 student grades tracker with a separate addition formula in each Total cell” title_text=”Student grades tracker with a separate addition formula in each Total cell” align=”center” _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n However, this method would soon be problematic in two recurrent circumstances:<\/p>\n By using the ARRAYFORMULA, I can quickly add up results for all students, regardless of the dataset size or the number of formulas used. I can add, remove, or edit content as I wish, and the formula will apply to the data automatically.<\/p>\n [\/et_pb_text][et_pb_text _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n Building on the previous example, in order to calculate the totals, you can use the ARRAYFORMULA as shown below. Notice that I am now adding the ranges, whereas before I added individual cells.<\/p>\n This new formula should look something like this:<\/p>\n [\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.24.1″ border_width_left=”4px” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n =ARRAYFORMULA(D2:D + E2:E + F2:F + G2:G)<\/p>\n [\/et_pb_text][et_pb_text _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n In order for the ARRAYFORMULA to function properly, make sure all the cells below it are empty.<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2022\/06\/ARRAY-\u2013-3.png” alt=”Google Sheets ARRAYFORMULA \u2014 one ARRAYFORMULA in I2 fills the whole Total column” title_text=”One ARRAYFORMULA in I2 fills the whole Total column” align=”center” _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n Please note:<\/b> By ending the range of each column with the letter, (D2:D) you can add the entire column as the array. This means that if you add any new rows of data in the columns, the ARRAYFORMULA function will automatically be applied.<\/p>\n As you can see, using the basic formula is very straightforward.<\/p>\n [\/et_pb_text][et_pb_text _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n You\u2019ve probably noticed a sequence of zeroes at the end of the column after applying the formula in the previous example. You can avoid this by adding an IF function to the formula.<\/p>\n This IF statement tells the formula to leave the \u2018Total\u2019 column blank if there is no name in column A.<\/p>\n [\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.24.1″ border_width_left=”4px” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n =ARRAYFORMULA(IF(A2:A<>\u201d\u201d, D2:D + E2:E + F2:F + G2:G, \u201d\u201d))<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2022\/06\/ARRAY-\u2013-4.png” alt=”Google Sheets ARRAYFORMULA \u2014 IF keeps the Total column blank where column A is empty” title_text=”IF keeps the Total column blank where column A is empty” align=”center” _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n Now there are only blank cells, without zeroes, after the last row of data in column A. Let\u2019s include a row for another student after row 50 to test its expandable nature.<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2022\/06\/ARRAY-\u2013-5.png” alt=”Google Sheets ARRAYFORMULA \u2014 Total shows 0 as soon as a new student is added in row 52″ title_text=”Total shows 0 as soon as a new student is added in row 52″ align=”center” _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n As soon as you start typing in content, the last column will automatically include a zero. This is a significant benefit of using ARRAYFORMULA. When working with a dataset that changes often, ARRAYFORMULA allows you to make changes without constantly adjusting the spreadsheet.<\/p>\n [\/et_pb_text][et_pb_text _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n Let\u2019s say I\u2019ve grouped the students into 3 different groups, and I want to find the group total on \u2018Project D\u2019. I can use the ARRAYFORMULA in conjunction with the SUMIF function to get the total from these 3 groups.<\/p>\n The syntax for SUMIF and ARRAYFORMULA is:<\/p>\n [\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.16″ border_width_left=”4px” global_colors_info=”{}” theme_builder_area=”post_content”]=ARRAYFORMULA(SUMIF(range, criteria, sum_range))[\/et_pb_text][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]For this example, I\u2019d use the formula:[\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.24.1″ border_width_left=”4px” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n =ARRAYFORMULA(SUMIF(B2:B, I2:I, H2:H))<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2022\/06\/ARRAY-\u2013-6.png” alt=”Google Sheets ARRAYFORMULA \u2014 SUMIF returns the Project D total for groups A, B and C” title_text=”SUMIF returns the Project D total for groups A, B and C” align=”center” _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n As you can see, I now have the total points per group for Project D, as shown in column J. I can use this new output to calculate any other evaluation metric in a quick and efficient way.<\/p>\n As before, zeroes appear sequenced after the last content cell in the \u2018Total\u2019 column. To hide them, wrap the SUMIF in the same IF test used in Example 2:<\/p>\n =ARRAYFORMULA(IF(I2:I<>"", SUMIF(B2:B, I2:I, H2:H), ""))<\/p>\n For more information on how the SUMIF function works, take a look at this blog post on How to use SUMIF in Google Sheets<\/a>.<\/p>\n [\/et_pb_text][et_pb_text _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n Let\u2019s say I want to look for specific performance data from a specific student. Usually, I could use the VLOOKUP function to do this. However, I would have to input the function multiple times for each criterion, as VLOOKUP only returns a single value. However, when I combine this with the ARRAYFORMULA, I can find multiple values at the same time.<\/p>\n The syntax for VLOOKUP with ARRAYFORMULA is:<\/p>\n [\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.16″ border_width_left=”4px” global_colors_info=”{}” theme_builder_area=”post_content”]=ARRAYFORMULA(VLOOKUP({search-key#1;search-key#2;…}, range, column-index,[sorted\/not-sorted])[\/et_pb_text][et_pb_text _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n In this example, I want to find the first name, last name and results for project A and B of student number 10. With \u2018Student ID\u2019 as the key, I would use the following formula:<\/p>\n [\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.24.1″ border_width_left=”4px” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n =ARRAYFORMULA(VLOOKUP($A$11, $A$1:$I$52, {1,3,4,5,6}, FALSE))<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2022\/06\/ARRAY-\u2013-7.png” alt=”Google Sheets ARRAYFORMULA \u2014 VLOOKUP returns the name and Project A and B scores of student 10″ title_text=”VLOOKUP returns the name and Project A and B scores of student 10″ align=”center” _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n To look up several keys at once, give VLOOKUP a range of search keys instead of one cell. With the student IDs you want in K2:K4, this formula returns one row of results for each ID:<\/p>\n =ARRAYFORMULA(VLOOKUP(K2:K4, $A$1:$I$52, {1,3,4,5,6}, FALSE))<\/p>\n For more information on how the VLOOKUP function works, check out this article on How the VLOOKUP function works in Google Sheets<\/a>.<\/p>\n [\/et_pb_text][et_pb_text _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n One of the most common uses when combining FILTER and ARRAYFORMULA is to identify duplicates. In order for it to function, you need to incorporate two basic formulas: COUNTIF and UNIQUE.<\/p>\n Let\u2019s say I want to find duplicate content for student last names to search for possible relatives. I would need to type in the following formula:<\/p>\n [\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.24.1″ border_width_left=”4px” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n =FILTER(UNIQUE(D2:D), ARRAYFORMULA(COUNTIF(D2:D, UNIQUE(D2:D))>1))<\/p>\n [\/et_pb_text][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]Any duplicate values should appear in the cell.[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2022\/06\/ARRAY-\u2013-8.png” alt=”Google Sheets ARRAYFORMULA \u2014 FILTER, UNIQUE and COUNTIF return the duplicate last name Dimmer” title_text=”FILTER, UNIQUE and COUNTIF return the duplicate last name Dimmer” align=”center” _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n As you can see, \u2018Dimmer\u2019 has appeared in the cell. This means that the formula found more than one cell in the \u2018Last Name\u2019 column containing the name Dimmer. This means that I have two students with that same last name.<\/p>\n You can also use this function to help find and remove duplicate data. To find out more about how these functions work individually, read these blog posts on the UNIQUE<\/a>, COUNTIF<\/a> and FILTER<\/a> functions.<\/p>\n [\/et_pb_text][et_pb_text _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n So far, the examples have all been applied to vertical arrays. Just as easily the ARRAYFORMULA can be used for horizontal arrays, which allows you to use it no matter the structure of your data.<\/p>\n Let\u2019s calculate the \u2018Total\u2019 for all projects for each student, but in a horizontal format. I can do this by inputting the following formula:<\/p>\n [\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.24.1″ border_width_left=”4px” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n =ARRAYFORMULA(B5:5 + B6:6 + B7:7 + B8:8)<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2022\/06\/ARRAY-\u2013-9.png” alt=”Google Sheets ARRAYFORMULA \u2014 horizontal array adds rows 5 to 8 into the Total row” title_text=”Horizontal array adds rows 5 to 8 into the Total row” align=”center” _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n Instead of telling the function to go down the list (e.g., from A2:A, which means starting at A2 and going down through column A), I\u2019ve told it to go across the data (e.g., from B5:5, which means starting at B5 and going across row 5).<\/p>\n By making this simple change, the ARRAYFORMULA now works horizontally. And just like in the previous example,\u00a0 I can add an IF statement to get rid of the zeroes in the blank columns:<\/p>\n [\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.24.1″ border_width_left=”4px” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n =ARRAYFORMULA(IF(B1:1<>\u201d\u201d, B5:5 + B6:6 + B7:7 + B8:8, \u201d\u201d))<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2022\/06\/ARRAY-\u2013-10.png” alt=”Google Sheets ARRAYFORMULA \u2014 horizontal array with IF, blank where row 1 is empty” title_text=”Horizontal array with IF, blank where row 1 is empty” align=”center” _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n You\u2019ve now seen the ARRAYFORMULA used in both vertical and horizontal orientations. But what about a table that includes both of these array types? You can use the ARRAYFORMULA for these tables too!<\/p>\n Here, I\u2019ve created a multiplication table that multiples column A by row 1, by simply typing the following:<\/p>\n [\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.24.1″ border_width_left=”4px” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n =ARRAYFORMULA(B1:M1 * A2:A13)<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2022\/06\/ARRAY-\u2013-11.png” alt=”Google Sheets ARRAYFORMULA \u2014 multiplication table from row 1 times column A” title_text=”Multiplication table from row 1 times column A” align=”center” _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n The ARRAYFORMULA not only helps to calculate numeric data, but it can also help to combine text from different columns.<\/p>\n Let\u2019s say I want to combine the \u2018First Name\u2019 and \u2018Last Name\u2019 columns to create a \u2018Full Name\u2019 column. I\u2019ve inserted a column for \u2018Full Name\u2019 to include the following formula:<\/p>\n [\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.24.1″ border_width_left=”4px” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n =ARRAYFORMULA(C2:C52 & \u201d \u201c & D2:D52)<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2022\/06\/ARRAY-\u2013-12.png” alt=”Google Sheets ARRAYFORMULA \u2014 First Name and Last Name combined into a Full Name column” title_text=”First Name and Last Name combined into a Full Name column” align=”center” _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]As you can see, I have obtained values from two different columns and combined them into one without the need to do manual work.<\/p>\n Please note:<\/b> This formula will only work if the two arrays are the same size (e.g.\u2018C2:C52&” “&D2:D52\u2019<\/b>). If not, Google Sheets won\u2019t be able to perform the calculation.[\/et_pb_text][et_pb_text module_id=”how-to-build-an-array-formula-step-by-st” _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n The examples above show finished formulas. This one builds an array formula from the first cell and adds ARRAYFORMULA at the end with the keyboard shortcut. The table lists sales agents and the sales amount each one made in 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.<\/p>\n 1.<\/strong> Open the spreadsheet with your data in Google Sheets.<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2026\/10\/google-sheets-array-formula-open-google-sheets-2.webp” alt=”Array formula in Google Sheets \u2014 Agent and Amount table with an empty 5% Bonus column” title_text=”Agent and Amount table with an empty 5% Bonus column” align=”center” _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n 2. In the first row of the column where you want the results, type the IF function<\/a>.<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2026\/10\/google-sheets-array-formula-add-if-function.webp” alt=”Array formula in Google Sheets \u2014 IF function typed in cell C2″ title_text=”IF function typed in cell C2″ align=”center” _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n 3. Add the condition: the amount in column B should be $4000 or more (B2:B6>=4000).<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2026\/10\/google-sheets-array-formula-add-condition.webp” alt=”Array formula in Google Sheets \u2014 condition B2:B6 greater than or equal to 4000 in the IF function” title_text=”Condition B2:B6 greater than or equal to 4000 in the IF function” align=”center” _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n 4. After the comma, add the calculation you want to return if the condition is TRUE.<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2026\/10\/google-sheets-array-formula-add-calculation-if-true.webp” alt=”Array formula in Google Sheets \u2014 B2:B6*0.05 added as the value if true” title_text=”B2:B6*0.05 added as the value if true” align=”center” _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n 5. Add another comma, followed by the value you want to return if the condition is FALSE.<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2026\/10\/google-sheets-array-formula-add-value-if-false.webp” alt=”Array formula in Google Sheets \u2014 empty text added as the value if false” title_text=”Empty text added as the value if false” align=”center” _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n 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.<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2026\/10\/google-sheets-array-formula-add-arrayformula.webp” alt=”Array formula in Google Sheets \u2014 ArrayFormula wrapped around the IF formula with the shortcut” title_text=”ArrayFormula wrapped around the IF formula with the shortcut” align=”center” _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n 7. Press \u2018Enter\u2019 to see the resulting array.<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2026\/10\/google-sheets-array-formula-results-array-3.webp” alt=”Array formula in Google Sheets \u2014 5% bonus for Bob, Janet, and Sarah, blank for Jane and Tim” title_text=”5% bonus for Bob, Janet, and Sarah, blank for Jane and Tim” align=”center” _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n The finished formula is =ARRAYFORMULA(IF(B2:B6>=4000,B2:B6*0.05,””)). Bob, Janet, and Sarah get their 5% bonus, and the cells for Jane and Tim stay blank.<\/p>\n [\/et_pb_text][et_pb_text _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n Sometimes you may need to add a column name above data processed by an ARRAYFORMULA function. Naming columns properly is key for clarity and collaboration. Fortunately, you can do this without altering the existing data structure.<\/p>\n For instance, suppose you have two lists of numerical values in columns A and B, and you want to add them together while including a column name. To do this, you can use the following formula:<\/p>\n [\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n ={“COLUMN NAME”; ARRAYFORMULA(A2:A11 + B2:B11)}<\/p>\n [\/et_pb_text][et_pb_text _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n There\u2019s a few things to keep in mind when doing this:<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2024\/04\/ARRAY-\u2013-13.webp” alt=”Google Sheets ARRAYFORMULA \u2014 COLUMN NAME header above the sum of List 1 and List 2″ title_text=”COLUMN NAME header above the sum of List 1 and List 2″ align=”center” _builder_version=”4.24.1″ _module_preset=”default” width=”100%” custom_margin=”30px||30px||true|false” border_radii=”on|12px|12px|12px|12px” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_text][et_pb_text _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n When an array formula stops working, the cause is usually one of these.<\/p>\n [\/et_pb_text][et_pb_text _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n And there you have it! The ARRAYFORMULA is an extremely powerful, versatile tool that can help significantly streamline your spreadsheet data.<\/p>\n Use it to return multiple values with just a single function. Also combine it with other functions such as IF, VLOOKUP, and SUMIF in order to create advanced formulas.<\/p>\n Are you looking to constrain an array result to a specific size? Learn How to use the ARRAY_CONSTRAIN formula in Google Sheets<\/a>.<\/p>\n [\/et_pb_text][et_pb_text _builder_version=”4.22.2″ background_color=”#f9f9ff” custom_margin=”40px||40px||false|false” custom_padding=”15px|25px|15px|25px|true|true” border_width_left=”3px” border_color_left=”#808e95″ global_module=”26540″ saved_tabs=”all” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n Editor\u2019s note<\/strong>: This is a revised version of a previous post that has been updated for accuracy and comprehensiveness.<\/em><\/p>\n [\/et_pb_text][\/et_pb_column][\/et_pb_row][\/et_pb_section]<\/p>\n","protected":false},"excerpt":{"rendered":" As its name suggests, ARRAYFORMULA turns a regular formula into an array formula in Google Sheets. It allows you to add formulas that expand automatically; they are applied as new data is introduced into the spreadsheet. That means you won\u2019t have to copy and paste formulas manually as new rows of data appear. At a […]<\/p>\n","protected":false},"author":41,"featured_media":27681,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"_et_pb_use_builder":"on","_et_pb_old_content":"To understand the importance and utility of the ARRAYFORMULA in Google Sheets, let us first go through a fundamental concept we already know.\n\nMany a time, We tend to use structurally similar formulas across the length of a column in a data range. In doing so, we take the advantage of 'relative referencing'. Meaning, the Google Sheets automatically adjusts the formula if we copy and paste it in the subsequent rows. Let us consider a basic example. The data set is a list of students along with their test scores. We should now calculate the totals in column F.\n\n\n
Why use ARRAYFORMULA in Google Sheets?<\/h2>\n
\n
How to use Google Sheets ARRAYFORMULA<\/h2>\n
\n
Example 1: Basic ARRAYFORMULA<\/h3>\n
Example 2: IF with ARRAYFORMULA<\/h3>\n
Example 3: SUMIF with ARRAYFORMULA<\/h3>\n
Example 4: VLOOKUP with ARRAYFORMULA<\/b><\/h3>\n
Example 5: FILTER with ARRAYFORMULA<\/h3>\n
Example 6: Horizontal arrays<\/h3>\n
Example 7: Multidimensional Arrays<\/h3>\n
Example 8: Using ARRAYFORMULA to combine columns<\/h3>\n
How to build an array formula step by step<\/h2>\n
How to add a column name even if there’s an ARRAYFORMULA in it<\/h2>\n
\n
Common ARRAYFORMULA errors and how to fix them<\/h2>\n
\n
How do I create an ARRAYFORMULA in Google Sheets?<\/h2>\n
![\"ARRAYFORMULA](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2017\/11\/array1.png\")
\n\nThe total score, for Alfred, is calculated using a simple formula \"=B2+C2+D2+F2\". We copy and paste this formula in the cells below to repeat such calculations for all the students. So, for Angela, the formula automatically becomes \"=B3+C3+D3+F3\". For Bob it is \"=B4+C4+D4+F4\", and so on and so forth. Though this approach is seemingly convenient, there are inherent problems to doing this.\n\n \t
\n \t
Syntax<\/h3>\nARRAYFORMULA(array_formula)<\/strong><\/span>\n
\n \t
\n \t
Usage: ARRAYFORMULA<\/h3>\nBuilding on the previous example, in order to calculate the totals, we can use the ARRAYFORMULA as shown below. Notice that, unlike before where we've added individual cells, we are now adding the ranges. And we key this in the very first total cell, F2. All the following Total cells should be clear of any values or formulas, so that ARRAYFORMULA can show us the results without any errors.\n\n
![\"ARRAYFORMULA](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2017\/11\/array2.png\")
\n\nTo quickly fix the zeroes at the end, we change the formula in just one place (i.e. cell F2). Yet, it affects the calculations across the range, as shown below. Notice that even the expression used in the condition within the IF formula is a range A2:A.\n\n![\"ARRAYFORMULA](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2017\/11\/array3.png\")
\n\nNow, We'll include a row for Charlotte below row # 7. Let's find out whether the ARRAYFORMULA automatically calculates the Total for her.\n\n![\"ARRAYFORMULA](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2017\/11\/array4.png\")
\n\nWell, not surprisingly, it does. For more information on ARRAYFORMULA, please check out this link<\/a>.\n\n\u00a0\n\n\u00a0","_et_gb_content_width":"","footnotes":""},"categories":[54],"tags":[39,28],"class_list":["post-2688","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-google-sheets-formulas","tag-connections-t","tag-spreadsheets"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.sheetgo.com\/fr\/wp-json\/wp\/v2\/posts\/2688","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sheetgo.com\/fr\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sheetgo.com\/fr\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sheetgo.com\/fr\/wp-json\/wp\/v2\/users\/41"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sheetgo.com\/fr\/wp-json\/wp\/v2\/comments?post=2688"}],"version-history":[{"count":0,"href":"https:\/\/www.sheetgo.com\/fr\/wp-json\/wp\/v2\/posts\/2688\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.sheetgo.com\/fr\/wp-json\/wp\/v2\/media\/27681"}],"wp:attachment":[{"href":"https:\/\/www.sheetgo.com\/fr\/wp-json\/wp\/v2\/media?parent=2688"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sheetgo.com\/fr\/wp-json\/wp\/v2\/categories?post=2688"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sheetgo.com\/fr\/wp-json\/wp\/v2\/tags?post=2688"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}