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 <\/span>use arrays in formulas that usually don\u2019t accept them, <\/b>including SUMIF, VLOOKUP, or FILTER.<\/span><\/p>\n Let\u2019s explore how ARRAYFORMULA works, and why you would need it in your life.<\/span><\/p>\n [\/et_pb_text][\/et_pb_column][\/et_pb_row][et_pb_row _builder_version=”4.27.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][et_pb_column type=”4_4″ _builder_version=”4.27.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][et_pb_text admin_label=”Connections T – One tool – Horizontal” module_class=”sheetgo-post-no-shadow-img md2-contained-button vertical-banner-container” _builder_version=”4.27.2″ _module_preset=”default” background_color=”#f2f7ff” max_width=”700px” module_alignment=”center” max_height=”300px” custom_margin=”20px|0px|20px|0px|true|true” custom_padding=”25px|25px|25px|25px|true|true” sticky_limit_bottom=”section” border_radii=”on|20px|20px|20px|20px” border_width_all=”1px” border_color_all=”#d9e7ff” global_colors_info=”{}” global_module=”50458″ theme_builder_area=”post_content”] One tool to merge, split, and filter all your spreadsheet data<\/span><\/p>\n Find out how<\/a>[\/et_pb_text][\/et_pb_column][\/et_pb_row][et_pb_row admin_label=”row” _builder_version=”4.16″ background_size=”initial” background_position=”top_left” background_repeat=”repeat” custom_padding=”27px|0px|27px|0|false|false” global_colors_info=”{}” theme_builder_area=”post_content”][et_pb_column type=”4_4″ _builder_version=”4.16″ custom_padding=”|||” global_colors_info=”{}” custom_padding__hover=”|||” theme_builder_area=”post_content”][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 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.<\/span><\/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.<\/span><\/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 0″ title_text=”ARRAY \u2013 1″ 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.<\/span><\/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 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.<\/span><\/p>\n As you can see, the ARRAYFORMULA syntax is simpler than expected. Let\u2019s explore more benefits of this function.<\/span><\/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.<\/span><\/p>\n Let\u2019s take a look at just a few:<\/span><\/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.<\/span><\/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.<\/span><\/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.<\/span><\/p>\n Usually, I would add up the cell values individually, as shown below:<\/span><\/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 1″ title_text=”ARRAY \u2013 2″ 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:<\/span><\/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.<\/span><\/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.<\/span><\/p>\n This new formula should look something like this:<\/span><\/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:E + G2:G)<\/span><\/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.<\/span><\/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 2″ title_text=”ARRAY \u2013 3″ 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.<\/span><\/p>\n As you can see, using the basic formula is very straightforward.<\/span><\/span><\/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.<\/span><\/p>\n This IF statement tells the formula to leave the \u2018Total\u2019 column blank if there is no name in column A.<\/span><\/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:E + G2:G, \u201d\u201d))<\/span><\/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 3″ title_text=”ARRAY \u2013 4″ 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.<\/span><\/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 4″ title_text=”ARRAY \u2013 5″ 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.<\/span><\/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.<\/span><\/p>\n The syntax for SUMIF and ARRAYFORMULA is:<\/span><\/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))<\/span><\/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 5″ title_text=”ARRAY \u2013 6″ 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.<\/span><\/p>\n As before, zeroes appear sequenced after the last content cell in the \u2018Total\u2019 column. Let\u2019s take a look at how combining SUMIFS and ARRAYFORMULA can help quickly fix this, as we did with the IF statement.<\/span><\/p>\n For more information on how the SUMIF function works, take a look at this blog post on How to use the SUMIF function in Excel<\/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.<\/span><\/p>\n The syntax for VLOOKUP with ARRAYFORMULA is:<\/span><\/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:<\/span><\/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))<\/span><\/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 6″ title_text=”ARRAY \u2013 7″ 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”]For more information on how the VLOOKUP function works, check out this article on How the VLOOKUP function works in Google Sheets<\/a>.[\/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.<\/span><\/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:<\/span><\/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))<\/span><\/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 7″ title_text=”ARRAY \u2013 8″ 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.<\/span><\/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:<\/span><\/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)<\/span><\/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 8″ title_text=”ARRAY \u2013 9″ 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).<\/span><\/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:<\/span><\/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))<\/span><\/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 9″ title_text=”ARRAY \u2013 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.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!<\/span><\/p>\n Here, I\u2019ve created a multiplication table that multiples column A by row 1, by simply typing the following:<\/span><\/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)<\/span><\/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 10″ title_text=”ARRAY \u2013 11″ 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.<\/span><\/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:<\/span><\/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)<\/span><\/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 11″ title_text=”ARRAY \u2013 12″ 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 _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.<\/span><\/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:<\/span><\/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)}<\/span><\/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:<\/span><\/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 13″ title_text=”ARRAY-\u2013-13″ 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.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.<\/span><\/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.<\/span><\/p>\n Are you looking to constrain an array result to a specific size? Learn <\/span>How to use the ARRAY_CONSTRAIN formula in Google Sheets<\/span><\/a>.<\/span><\/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":" Like its name suggests, ARRAYFORMULA is an array function. 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 fundamental level, it takes the values from […]<\/p>\n","protected":false},"author":41,"featured_media":27681,"comment_status":"open","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![\"\"](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2024\/09\/connections-top-processors-main.webp\")
<\/p>\n![\"39\"](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2024\/09\/Recommended-for-Google-Workspace-badge.webp\"){kind=icon}
<\/p>\nWhat is the ARRAYFORMULA?<\/h2>\n
Syntax<\/h3>\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<\/span><\/h3>\n
Example 3: SUMIF with ARRAYFORMULA<\/span><\/h3>\n
Example 4: VLOOKUP with ARRAYFORMULA<\/b><\/h3>\n
Example 5: FILTER with ARRAYFORMULA<\/span><\/h3>\n
Example 6: Horizontal arrays<\/span><\/h3>\n
Example 7: Multidimensional Arrays<\/span><\/h3>\n
Example 8: Using ARRAYFORMULA to combine columns<\/span><\/h3>\n
How to add a column name even if there’s an ARRAYFORMULA in it<\/span><\/h2>\n
\n
How do I create an ARRAYFORMULA in Google Sheets?<\/span><\/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}]}}