Cara menggunakan google sheets concatenate arrayformula

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. 

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 ARRAYFORMULA. 

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 

“the display of values returned from an array formula into multiple rows and/or columns and the use of non-array functions with arrays”.

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 Ctrl+Shift+Enter shortcut (Cmd + Shift + Enter on a Mac), while your cursor is in the formula bar to make a formula an array formula (Google Sheets will automatically add ARRAYFORMULA to the start of the formula). 

Read more about Google Sheets shortcuts.

If you prefer watching to reading, check out this video tutorial by Railsware Product Academy about ARRAYFORMULA in Google Sheets

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

=ARRAYFORMULA(array_formula)

array_formula is a parameter that can be

• 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 example

Let’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 a new range for Product A1.

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 functions

If 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.

Coupler.io is a data importing solution that provides many Google Sheets integrations for such apps as Jira, HubSpot, Airtable, and many others. It allows you to automate data flow on a schedule and use Excel and BigQuery as a destination in addition to Google Sheets.

Google Sheets ARRAYFORMULA with IF function

To 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 Sheets

Building 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 Sheets

So, 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 expand

With SUMIFS, things are a little bit more complicated. Its syntax is:

=SUMIFS(sum_range, range1, criteria1, [range2], [criteria2], ...)

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 SUMIF

Actually, 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, QUERY

Another 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 Sheets

For 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])

=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 “{}” to indicate the columns we want to return, and apply ARRAYFORMULA, so Google Sheets knows we’re working with a range output, not a single value. My data table is in the range A2:G17 and the search value is in A13, so the formula will be as follows:

=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: {2,5,6}

VLOOKUP and ARRAYFORMULA Google Sheets in a real-life example

Let’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. 

Check out also the Pipedrive to Excel integration.

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 person_id.name is needed to vertically look up the data.

This is how the imported Deals dataset looks:

Step 2: Apply the VLOOKUP formula nested with ARRAYFORMULA in Google Sheets

Once 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 Sheets

Another 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 Sheets

ARRAYFORMULA 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 output 

When 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, “Hip, Hip Array!”

Back to Blog