Let’s imagine a situation where you’ve got 2 columns in a Google spreadsheet: the 1st with prices, the 2nd with the number of items, and you need to multiply them in the 3rd column. What do you usually do in this case? If you were like me in the past, you’d compose a formula in the first row and copy-paste it into the other rows. A good old-school method that works fine. Show - What is ARRAYFORMULA in Google Sheets?
- How does Google Sheets ARRAYFORMULA solve the problems?
- Google Sheets ARRAYFORMULA syntax
- Google Sheets ARRAYFORMULA example
- ARRAYFORMULA with different Google Sheets functions
- Google Sheets ARRAYFORMULA with IF function
- SUMIF & SUMIFS with ARRAYFORMULA in Google Sheets
- SUMIF + ARRAYFORMULA in Google Sheets
- SUMIFS + ARRAYFORMULA Google Sheets does not expand
- Can I use QUERY as an alternative to SUMIFS+ARRAYFORMULA in Google Sheets?
- VLOOKUP and ARRAYFORMULA in Google Sheets
- VLOOKUP and ARRAYFORMULA Google Sheets in a real-life example
- FILTER and ARRAYFORMULA in Google Sheets
- How to use ARRAYFORMULA to combine columns in Google Sheets
- Blank cells challenge in Google Sheets ARRAYFORMULA output
- That’s the curtain?
- Focus on your business goals while we take care of your data!
But what if there are 1000 rows or even more? Annoying, right? Let alone time-consuming. It can also cause a performance issue since a bunch of similar formulas slow down the whole spreadsheet. And, if you need to add a new value and create a separate row for it, Google Sheets will not automatically copy the formula. OK, so what’s the solution here? Actually, there is a dynamic and efficient way to address the discussed issues, and this way is called ## What is ARRAYFORMULA in Google Sheets?In short, ARRAYFORMULA is a function that outputs a range of cells instead of just a single value and can be used with non-array functions. According to Google Sheets documentation, ARRAYFORMULA enables
Well, the definition kills any desire to use the function, but wait, do not jump to a conclusion. It is tremendously useful and easier to use than it sounds in the description. To use it in Google Sheets, you can either directly type “ARRAYFORMULA” or hit a Read more about Google Sheets shortcuts.
## How does Google Sheets ARRAYFORMULA solve the problems?- Since this is one single formula even for a huge dataset, you won’t end up with a lot of formulas, and your Google Sheets will run smoothly.
- ARRAYFORMULA is also expandable – a change in one place will expand down the entire data range.
- And it is dynamic as well. When a new row is introduced into the dataset, the formula will automatically be applied to it.
## Google Sheets ARRAYFORMULA syntax
- a range
- a mathematical expression using ranges of the same size, or
- a function that returns a result greater than one cell
## Google Sheets ARRAYFORMULA exampleLet’s have a closer look at how the ARRAYFORMULA works. The easiest way to understand this is through an example. Let’s say we have a dataset showing the quantity of four different products sold in the summer months and we need to calculate the total amount of sold products. Sure, we could do it by writing a formula in column E that adds B, C, and D. =B2+C2+D2 Or use the SUM function. To find the sold quantity of B, C, and D products, you can copy the formula in E2 and then paste it into the cells E3, E4, and E5 and then use SUM at the bottom of column E. =sum(E2:E5) However, the ARRAYFORMULA function lets you skip all those steps and get straight to the answer with a single formula, which saves you time and energy if you’ve got 1000+ products. In our case we will have =ArrayFormula( sum(B2:B5+C2:C5+D2:D5) ) Now, I am adding ARRAYFORMULA takes into account the new range (changes B5 to B6, C5 to C6, D5 to D6 in the formula), and does the calculation, unlike SUM, which is expected for Google Sheets. Now the formula looks as follows: =ArrayFormula( sum(B2:B6+C2:C6+D2:D6) ) ## ARRAYFORMULA with different Google Sheets functionsIf a formula already returns an array of values, wrapping it up with ARRAYFORMULA is not necessary. This means that combining ARRAYFORMULA with, for example, FILTER, SEQUENCE, or QUERY, won’t bring any value. At the same time, you cannot benefit from nesting ARRAYFORMULA with many non-array functions including: - SUM
- SUMIFS
- COUNT
- COUNTA
- COUNTIFS
- CONCATENATE
- JOIN
- TEXTJOIN
- etc.
However, as we mentioned before, ARRAYFORMULA can be used with non-array functions, for example, IF, SUMIF, COUNTIF, VLOOKUP, and others. We’ll check out how they work below, but first, let’s import some datasets that we can use as examples. To do this, we set up a OneDrive to Google Sheets integration provided by Coupler.io.
## Google Sheets ARRAYFORMULA with IF functionTo remind you, the IF function in Google Sheets works by performing a logical test that can only have one of two outcomes: true or false. Read more about IF and other logical functions in Google Sheets, Let’s see how to use the IF function and ARRAY on the sales spreadsheet. Consider a standard IF statement that checks whether there are enough (more than 10) items left in stock for next month. In cell G2, I’d like to display the text “order” if there are fewer than ten items left in stock, and “no” if the outcome is false. =if(C2<10, "order", "no") The IF function does its calculation and, for this first item, since there are only eight left in stock, the text “order” is displayed. Now let’s run the test for each item, and this is where a single ARRAYFORMULA comes in handy. Type ARRAYFORMULA before IF, and it runs the IF statement across all the rows at once. Cool, right? =arrayformula( if(C2:C17<10, "order", "no") ) ## SUMIF & SUMIFS with ARRAYFORMULA in Google SheetsBuilding on the previous example, let’s have a look at how ARRAYFORMULA can be used with SUMIF and SUMIFS Google Sheet functions. SUMIF and SUMIFS are two independent functions in Google Sheets. SUMIF is used for adding values based on one condition and the purpose of SUMIFS is to sum the values in a range, based on multiple conditions. ## SUMIF + ARRAYFORMULA in Google SheetsSo, let’s code an array formula for SUMIF. Let’s say you need to find out how many stationery items have been already ordered and you apply SUMIF function, which returns you 32 in cell I2. =sumif(F2:F,H2:H,D2:D) And if you want to know the number of items already ordered for each category, the best way is to apply ARRAYFORMULA, which again will be extremely helpful if you have way too many categories. =arrayformula( sumif(F2:F,H2:H,D2:D) ) ## SUMIFS + ARRAYFORMULA Google Sheets does not expandWith SUMIFS, things are a little bit more complicated. Its syntax is:
Unlike SUMIF, the SUMIFS function does not expand the results even if you use ARRAYFORMULA with it. The logic is simple, since SUMIFS in Google Sheets returns the sum of an array conditionally, so it can be nothing but a single result. Let’s check it out. =sumifs(D2:D17,F2:F17,H2:H4,G2:G17,I2:I4) The SUMIFS in the above example sums up the amount of “Stationery” items that need to be ordered. And, even if we nest SUMIFS with ARRAYFORMULA, it won’t expand but will return a single result one way or another. =ArrayFormula( sumifs(D2:D17,F2:F17,H2:H4,G2:G17,I2:I4) ) To solve this expanding issue, one should use alternative formulas. There are several options on how to address SUMIFS-ARRAYFORMULA-expansion issues, and the easiest one is with the help of SUMIF. ## Alternative #1: Google Sheets ARRAYFORMULA and SUMIFActually, the SUMIF function can handle multiple criteria to expand the results, though in a slightly tricky way. The main tip here is to combine ranges and corresponding criteria using AMPERSAND ( =ArrayFormula( sumif(F2:F17&G2:G17,H2:H4&I2:I4,D2:D17) ) And, as you can see, it is perfectly expandable. ## Alternative #2: Google Sheets ARRAYFORMULA, IF, LEN, VLOOKUP, QUERYAnother workaround is to use the combination of ARRAYFORMULA, IF, LEN, VLOOKUP, and QUERY functions. Looks complicated? Well, actually, it is. But the formula works perfectly, no doubt. ={"To order"; ARRAYFORMULA(IF(LEN(H2:H)=0,, VLOOKUP( H2:H&I2:I, QUERY( {F:F&G:G,D:D}, "SELECT Col1, SUM(Col2) WHERE Col1 IS NOT NULL GROUP BY Col1" ), 2, false ) )) } ## Can I use QUERY as an alternative to SUMIFS+ARRAYFORMULA in Google Sheets?You may come up with the idea of trying to use something simpler, like QUERY solo, as some other internet resources suggest. Well, we tried to apply the following formula: =query(A2:G,"Select sum(D) where F='"&H2&"'and G='"&I2&"' or F='"&H3&"'and G='"&I3&"' or F='"&H4&"'and G='"&I4&"' group by F,G label sum(D)''") And logically it should have worked, but it hasn’t as it returns results in random order, which is not user-friendly at all, to say the least. So, if you have no prejudices or limitations about using SUMIF, better check out the Alternative #1. Read more about the power of QUERY function in Google Sheets. ## VLOOKUP and ARRAYFORMULA in Google SheetsFor a lot of Google sheets users, mastering VLOOKUP is the turning point. That’s when they are really getting comfortable with many functions and their application. And, if you still haven’t mastered it, open up VLOOKUP by reading our dedicated post VLOOKUP Explained: How to Search Data Vertically in Spreadsheets. As you are probably aware, the main limiting problem with VLOOKUP is that it only allows you to look for a single value. However, the real world often requires you to use two or more criteria when looking up data from a database. To vertically lookup multiple criteria, nest VLOOKUP with ARRAYFORMULA: =ARRAYFORMULA(VLOOKUP({search-key#1;search-key#2;…}, range, column-index,[sorted/not-sorted])
Let’s have a look at the already-familiar table, assuming we want to search for Item SKU# and return Amount Sold, Price and Category. Since we need VLOOKUP to return multiple columns, let’s use curly brackets “ =ArrayFormula( vlookup($A$13,$A$2:$G$17,{2,5,6},FALSE) ) In fact, this is a regular VLOOKUP formula but, instead of a single
column, we use an array of columns in curly brackets: ## VLOOKUP and ARRAYFORMULA Google Sheets in a real-life exampleLet’s also consider another, more advanced case. For example, let’s see how Pipedrive users can import mixed data (some fields from one entity and some fields from another) with the help of the ARRAYFORMULA-VLOOKUP combination. Pipedrive, being an excellent tool itself, still doesn’t offer enough options for reports customization and data flow configuration. And to be able to use the reporting power of spreadsheets and at the same time avoid manual copy-pasting, we set up a dedicated integration supported by Coupler.io. Pipedrive to Google Sheets integration, in particular, enables you to pull data separately from the following entities: Deals, Persons, Organizations, Activities and Files.
Though sometimes you may need to extract data from different Pipedrive entities and combine it into a single sheet. In such a case, ARRAYFORMULA combined with VLOOKUP fits the bill. Let’s see how it does the job. The first thing to do is to import fields: - two fields from Persons (name, org_id.name, and org_id.address)
- one field from Deals (formatted_value).
## Step 1: Set up two Pipedrive importers:Refer to Coupler.io documentation for details about how to set up a Pipedrive integration. ## Parameters for the Pipedrive importer #1:- Data entity:
`Persons` - Fields:
`name, org_id.name, org_id.address` - When configuring this importer, navigate to Destination => Show advanced => type
`B1` into the “`Cell address` ” field
This is how the imported Persons dataset looks: ## Parameters for the Pipedrive importer #2:- Data entity:
`Deals` - Fields:
`person_id.name, formatted_value`
The field This is how the imported Deals dataset looks: ## Step 2: Apply the VLOOKUP formula nested with ARRAYFORMULA in Google SheetsOnce you’ve pulled the data, apply the following VLOOKUP formula to the A1 cell in Pipedrive Persons sheet: ={"formatted_value"; iferror( arrayformula( vlookup(B2:B,'Pipedrive Deals'!A2:B,2,false) ) ) } No doubt, the ARRAYFORMULA-VLOOKUP combination, when used properly, is a tool that can save you tons of time and spare you a lot of busywork. ## FILTER and ARRAYFORMULA in Google SheetsAnother popular and useful function that proves beneficial when you need to find information quickly is the FILTER function. It is used to conditionally filter the specified data range to get the required info. This Google Sheets function has already been explained in the FILTER How-To Guide, but let’s reconsider the application of FILTER with ARRAYFORMULA. In the sales spreadsheet, let’s filter out duplicates – in our case, the identical sales numbers. Since there is no direct function in Google Sheets to cope with the task, the optimal workaround will be to use FILTER with UNIQUE, ARRAYFORMULA, and COUNTIF: =filter( unique(C2:C), arrayformula( countif(C2:C,unique(C2:C))>1 ) ) As you can see, the formula meets the challenges and returns one duplicate. ## How to use ARRAYFORMULA to combine columns in Google SheetsARRAYFORMULA also helps you do manipulation with a text. You can actually combine a text with a text, a text with a number, and a text with a date in Google Sheets and apply ARRAYFORMULA to that combination. For example, if we have a list of, let’s say, sales managers, and need to combine the first and last names. To get the name and the surname of the first sales manager, use the following formula: =A2 &" "& B2 The full name appears in a single cell, E2. Read more about Merging Data in Google Sheets. So, let’s use the ARRAYFORMULA function to have all the names and surnames coupled. =arrayformula(A2:A14 &" "& B2:B14) Applied only in E2, the formula automatically expanded to the other cells below. ## Blank cells challenge in Google Sheets ARRAYFORMULA outputWhen you work with the ARRAYFORMULA function, you have to be careful with the array sizes. They should always be the same, for example, F2:F17&G2:G17. Otherwise Google Sheets won’t carry out the calculation. As an option, not to sweat too much, you may use the infinite range, as we did with SUMIF. =arrayformula( sumif(F2:F,H2:H,D2:D) ) But, in this case, you may face another challenge – extra blank cells in your formula output. In my instance with ARRAYFORMULA+SUMIF in I2, if there is the limited range H2:H4, all will work well. Though, if you change it to H2:H, SUMIF will treat this range as the one containing the criteria and will sum the column accordingly. Well, it may spoil the looks, let alone litter up the whole spreadsheet, but, most importantly, when you enter any value in any of those cells, the formula will return a #REF! Error. And this happens not only with SUMIF, but with other formulas as well. So, let’s see how to remove them. So, to remove extra blank cells returned by ARRAYFORMULA nested with SUMIF in Google Sheets, we can use the FILTER function to filter out blank cells in the criteria that cause the extra zeros and the blank cells correspondingly. =ArrayFormula( sumif(F2:F,filter(H2:H,H2:H<>""),D2:D) ) Or, alternatively, you can use IF+LEN and it will do the same job. =ArrayFormula( if(len(H2:H),(sumif(F2:F,H2:H,D2:D)),) ) But, unlike the option with FILTER, IF+LEN won’t help to avoid the issue with #REF! Error and will also return it if you enter any value in blank cells, so be careful! ## That’s the curtain?Hardly. The Google Sheet ARRAYFORMULA function is a really multi-purpose tool and can be used with many other combinations and applications not covered here. If you have any in mind and want to discuss them, comment below and we’ll elaborate. As for now, good luck with you data, and as Ben Collins, Google Sheets developer and data
analytics instructor, wrote in his blog, Back to Blog ## Focus on your business goals while we take care of your data!Try Coupler.io |