{"id":457541,"date":"2022-06-03T18:00:01","date_gmt":"2022-06-03T15:00:01","guid":{"rendered":"https:\/\/en.buradabiliyorum.com\/how-to-use-the-arrayformula-function-in-google-sheets\/"},"modified":"2022-06-03T18:00:01","modified_gmt":"2022-06-03T15:00:01","slug":"how-to-use-the-arrayformula-function-in-google-sheets","status":"publish","type":"post","link":"https:\/\/buradabiliyorum.com\/en\/how-to-use-the-arrayformula-function-in-google-sheets\/","title":{"rendered":"#How to Use the ARRAYFORMULA Function in Google Sheets"},"content":{"rendered":"<div id=\"ez-toc-container\" class=\"ez-toc-v2_0_84 counter-hierarchy ez-toc-counter ez-toc-custom ez-toc-container-direction\">\n<p class=\"ez-toc-title\" style=\"cursor:inherit\">Table of Contents<\/p>\n<label for=\"ez-toc-cssicon-toggle-item-6a2f07f19ba8c\" class=\"ez-toc-cssicon-toggle-label\"><span class=\"\"><span class=\"eztoc-hide\" style=\"display:none;\">Toggle<\/span><span class=\"ez-toc-icon-toggle-span\"><svg style=\"fill: #dd3333;color:#dd3333\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" class=\"list-377408\" width=\"20px\" height=\"20px\" viewBox=\"0 0 24 24\" fill=\"none\"><path d=\"M6 6H4v2h2V6zm14 0H8v2h12V6zM4 11h2v2H4v-2zm16 0H8v2h12v-2zM4 16h2v2H4v-2zm16 0H8v2h12v-2z\" fill=\"currentColor\"><\/path><\/svg><svg style=\"fill: #dd3333;color:#dd3333\" class=\"arrow-unsorted-368013\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"10px\" height=\"10px\" viewBox=\"0 0 24 24\" version=\"1.2\" baseProfile=\"tiny\"><path d=\"M18.2 9.3l-6.2-6.3-6.2 6.3c-.2.2-.3.4-.3.7s.1.5.3.7c.2.2.4.3.7.3h11c.3 0 .5-.1.7-.3.2-.2.3-.5.3-.7s-.1-.5-.3-.7zM5.8 14.7l6.2 6.3 6.2-6.3c.2-.2.3-.5.3-.7s-.1-.5-.3-.7c-.2-.2-.4-.3-.7-.3h-11c-.3 0-.5.1-.7.3-.2.2-.3.5-.3.7s.1.5.3.7z\"\/><\/svg><\/span><\/span><\/label><input type=\"checkbox\"  id=\"ez-toc-cssicon-toggle-item-6a2f07f19ba8c\" checked aria-label=\"Toggle\" \/><nav><ul class='ez-toc-list ez-toc-list-level-1 ' ><li class='ez-toc-page-1 ez-toc-heading-level-1'><a class=\"ez-toc-link ez-toc-heading-1\" href=\"https:\/\/buradabiliyorum.com\/en\/how-to-use-the-arrayformula-function-in-google-sheets\/#%E2%80%9CHow_to_Use_the_ARRAYFORMULA_Function_in_Google_Sheets%E2%80%9D\" >&#8220;How to Use the ARRAYFORMULA Function in Google Sheets&#8221;<\/a><ul class='ez-toc-list-level-2' ><li class='ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-2\" href=\"https:\/\/buradabiliyorum.com\/en\/how-to-use-the-arrayformula-function-in-google-sheets\/#About_Array_Formulas\" >About Array Formulas<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-3\" href=\"https:\/\/buradabiliyorum.com\/en\/how-to-use-the-arrayformula-function-in-google-sheets\/#Use_the_ARRAYFORMULA_in_Google_Sheets\" >Use the ARRAYFORMULA in Google Sheets<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-4\" href=\"https:\/\/buradabiliyorum.com\/en\/how-to-use-the-arrayformula-function-in-google-sheets\/#Basic_ARRAYFORMULA_Examples\" >Basic ARRAYFORMULA Examples<\/a><\/li><\/ul><\/li><\/ul><\/nav><\/div>\n<h1><span class=\"ez-toc-section\" id=\"%E2%80%9CHow_to_Use_the_ARRAYFORMULA_Function_in_Google_Sheets%E2%80%9D\"><\/span>&#8220;How to Use the ARRAYFORMULA Function in Google Sheets&#8221;<span class=\"ez-toc-section-end\"><\/span><\/h1>\n<div>\n<img loading=\"lazy\" decoding=\"async\" class=\"type:primaryImage alignnone size-full wp-image-731741\" data-pagespeed-lazy-srcset=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2021\/05\/google_sheets_her_1200x675.jpg?width=398&amp;trim=1,1&amp;bg-color=000&amp;pad=1,1 400w, https:\/\/www.howtogeek.com\/wp-content\/uploads\/2021\/05\/google_sheets_her_1200x675.jpg?width=1198&amp;trim=1,1&amp;bg-color=000&amp;pad=1,1 1200w\" sizes=\"auto, 400w, 1200w\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2021\/05\/google_sheets_her_1200x675.jpg?width=1198&amp;trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"\" width=\"1200\" height=\"675\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>How many times have you created a formula and copied it to the adjacent cells in your sheet? In Google Sheets, you can skip this step by using the ARRAYFORMULA function.<\/p>\n<p>With ARRAYFORMULA in Google Sheets, you can return several values instead of just one. This lets you combine the function with others like SUM, SUMIF, IF, and more to obtain results for an entire cell range.<\/p>\n<h2 role=\"heading\" aria-level=\"2\"><span class=\"ez-toc-section\" id=\"About_Array_Formulas\"><\/span>About Array Formulas<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>If you aren\u2019t familiar with array formulas, you can simply break down the term. An array is a range of cells arranged in rows and columns. A formula is an equation of sorts that performs an action or calculation on the referenced cell(s).<\/p>\n<p>So when you combine the two, an array formula lets you perform multiple calculations on a group of cells at once. You can obtain a single result or multiple results with an array formula based on the calculations you perform. But many find the greater value in the latter.<\/p>\n<p>To make using array formulas easier, Google Sheets provides a function dedicated to it, the ARRAYFORMULA function.<\/p>\n<h2 role=\"heading\" aria-level=\"2\"><span class=\"ez-toc-section\" id=\"Use_the_ARRAYFORMULA_in_Google_Sheets\"><\/span>Use the ARRAYFORMULA in Google Sheets<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>The syntax for the function is <code>ARRAYFORMULA(array_formula)<\/code> where the one and only argument is required. The argument can include a cell range, expression, or function for one or more arrays of the same size.<\/p>\n<p>There are two ways to insert an ARRAYFORMULA formula in Google Sheets.<\/p>\n<p>This first method is ideal when you\u2019ve already typed your formula and realize you want to use the ARRAYFORMULA function instead. Or, for those times when you want to concentrate on the meat of the formula and worry about adding the ARRAYFORMULA function afterward.<\/p>\n<p>With an ordinary formula entered into a cell, place your cursor in or on the formula in the Formula Bar. Then, press Ctrl+Shift+Enter on Windows or Command+Shift+Return on Mac. You\u2019ll see your formula transform into an ARRAYFORMULA formula.<\/p>\n<p>Simply press Enter or Return to <a href=\"https:\/\/buradabiliyorum.com\/en\/category\/download-scripts-themes-apps\/\" data-internallinksmanager029f6b8e52c=\"9\" title=\"Download Scripts &amp; Themes &amp; Apps\" target=\"_blank\" rel=\"noopener\">app<\/a>ly the converted formula.<\/p>\n<p>The next method for inserting an ARRAYFORMULA formula in Google Sheets is by entering it like any other formula. So, let\u2019s look at some basic examples.<\/p>\n<h2 role=\"heading\" aria-level=\"2\"><span class=\"ez-toc-section\" id=\"Basic_ARRAYFORMULA_Examples\"><\/span>Basic ARRAYFORMULA Examples<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>For this first example, we\u2019ll do a simple multiplication calculation for a cell range. We\u2019ll take our Quantity Sold and multiply it by the Unit Price. To do this for our entire array, we would use the following formula:<\/p>\n<pre>=ARRAYFORMULA(B2:B6*C2:C6)<\/pre>\n<p>While we have a small cell range for our calculation here, cells B2 through B6 multiplied by cells C2 through C6, imagine if you have hundreds of cells in the array. Rather than inserting a formula that you then need to copy down, just use the ARRAYFORMULA for the array.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-805827\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2022\/05\/Multiply-GoogleSheetsARRAYFORMULA.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"ARRAYFORMULA with multiplication in Google Sheets\" width=\"495\" height=\"185\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>For this next example, let\u2019s toss in another function. We\u2019ll add the formula for the IF function as the argument for ARRAYFORMULA. Using the formula below, we will display Bonus if the amount in the cell range F2 through F6 is above 20,000 and No Bonus if it\u2019s not.<\/p>\n<pre>=ARRAYFORMULA(IF(F2:F6&gt;20000,\"Bonus\", \"No Bonus\"))<\/pre>\n<p>Again, we save a step by inserting a single formula that populates for the entire cell range.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-805826\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2022\/05\/IF-GoogleSheetsARRAYFORMULA.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"ARRAYFORMULA with IF in Google Sheets\" width=\"525\" height=\"180\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>In our final example, we\u2019ll combine the SUMIF function with ARRAYFORMULA. Using the formula below, we sum the amounts in cells M2 through M16 if the values in cells O3 through O5 equal those in cells L2 through L16.<\/p>\n<pre>= ARRAYFORMULA(SUMIF(L2:L16,O3:O5,M2:M16))<\/pre>\n<p>Now with this one simple formula, we\u2019re able to get sales totals for only those three products we want. The formula for the ARRAYFORMULA function fills our cells for Shirts, Shorts, and Shoes correctly.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-805825\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2022\/05\/SUMIF-GoogleSheetsARRAYFORMULA.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"ARRAYFORMULA with SUMIF in Google Sheets\" width=\"650\" height=\"386\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>Here you have the basics of using the ARRAYFORMULA function in Google Sheets. So, you can experiment with more complex formulas to achieve the results you need.<\/p>\n<p>Keep in mind this is one of those Google Sheets functions currently unavailable in Microsoft Excel, so take advantage of it!<\/p>\n<\/div>\n<p><script>\n setTimeout(function(){\n  !function(f,b,e,v,n,t,s)\n  {if(f.fbq)return;n=f.fbq=function(){n.callMethod?\n  n.callMethod.apply(n,arguments):n.queue.push(arguments)};\n  if(!f._fbq)f._fbq=n;n.push=n;n.loaded=!0;n.version='2.0';\n  n.queue=[];t=b.createElement(e);t.async=!0;\n  t.src=v;s=b.getElementsByTagName(e)[0];\n  s.parentNode.insertBefore(t,s) } (window, document,'script',\n  'https:\/\/connect.facebook.net\/en_US\/fbevents.js');\n   fbq('init', '335401813750447');\n   fbq('track', 'PageView');\n  },3000);\n<\/script><\/p>\n<blockquote><p><strong><span style=\"color: #ff6600;\">If you liked the article, do not forget to share it with your friends. Follow us on\u00a0<span style=\"color: #ff0000;\"><a style=\"color: #ff0000;\" href=\"https:\/\/news.google.com\/publications\/CAAqBwgKMLG0nwswvr63Aw\" target=\"_blank\" rel=\"nofollow noopener noreferrer\">Google News<\/a><\/span>\u00a0too, click on the star and choose us from your favorites.<\/span><\/strong><\/p><\/blockquote>\n<blockquote>\n<p style=\"text-align: center;\">For forums sites go to <span style=\"color: #ff9900;\"><a style=\"color: #ff9900;\" href=\"https:\/\/forum.buradabiliyorum.com\/\" target=\"_blank\" rel=\"noopener\">Forum.BuradaBiliyorum.Com<\/a><\/span><\/strong><\/p>\n<\/blockquote>\n<blockquote>\n<p style=\"text-align: center;\"><strong>If you want to read more like this article, you can visit our <span style=\"color: #ff9900;\"><a style=\"color: #ff9900;\" href=\"https:\/\/en.buradabiliyorum.com\/technology\/\" target=\"_blank\" rel=\"noopener\">Technology category.<\/a><\/span><\/strong><\/p>\n<\/blockquote>\n<p><span style=\"color: black;\"><a style=\"color: #ff9900;\" href=\"https:\/\/www.howtogeek.com\/805792\/arrayformula-google-sheets\/\" target=\"_blank\" rel=\"noopener\">Source<\/a><\/span><\/p>\n","protected":false},"excerpt":{"rendered":"<p>&#8220;How to Use the ARRAYFORMULA Function in Google Sheets&#8221; How many times have you created a formula and copied it to the adjacent cells in your sheet? In Google Sheets, you can skip this step by using the ARRAYFORMULA function. With ARRAYFORMULA in Google Sheets, you can return several values instead of just one. This&#8230;<\/p>\n","protected":false},"author":1,"featured_media":457542,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"fifu_image_url":"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2021\/05\/google_sheets_her_1200x675.jpg?height=200p&trim=2,2,2,2","fifu_image_alt":"","footnotes":""},"categories":[18],"tags":[],"class_list":["post-457541","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-technology"],"_links":{"self":[{"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/posts\/457541","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/comments?post=457541"}],"version-history":[{"count":0,"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/posts\/457541\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/media\/457542"}],"wp:attachment":[{"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/media?parent=457541"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/categories?post=457541"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/tags?post=457541"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}