In this post I’m going to run through the basics of using array formulas, and you’ll see they’re really not that scary. Hip, Hip Array!

# What are array formulas in Google Sheets?

First of all, what are they?

To the uninitiated, they’re mysterious. Elusive. Difficult to understand. Yes, yes, and yes, but they are *incredibly useful* in the right situations.

Per the official definition, array formulas enable *the display of values returned into multiple rows and/or columns and the use of non-array functions with arrays*.

**In a nutshell: whereas a normal formula outputs a single value, array formulas output a range of cells!**

The easiest way to understand this is through an example.

Imagine we have this dataset, showing the quantity and item cost for four products:

and we want to calculate the total cost of all four products.

We could easily do this by adding a formula in column D that multiplies B and C, and then add a sum at the bottom of column D.

However, array formulas let us skip that step and get straight to the answer with a single formula.

## What’s the formula?

`=ArrayFormula(SUM(B2:B5 * C2:C5))`

## How does this formula work?

Ordinarily, when we use the multiplication (*) operator in a Sheet, we give it two numbers or two cells to multiply together.

However, in this case we’re giving it two ranges, or two arrays, of data:

`= B2:B5 * C2:C5`

However, when we hit Enter this gives us a #VALUE! error as shown here:

We need to tell Google Sheets we want this to be an Array Formula. We do this in two ways.

Either type in the word `ArrayFormula`

and add an opening/closing brackets to wrap your formula, or, more easily, just hit **Ctrl + Shift + Enter** (**Cmd + Shift + Enter** on a Mac) and Google Sheets will add the ArrayFormula wrapper for us.

`=ArrayFormula(B2:B5 * C2:C5)`

Now it works, and Google Sheets will output an array with each cell corresponding to a row in the original arrays, as shown in the following image:

Effectively what’s happening is that for each row, Google does the calculation and includes that result in our output array (here showing the equivalent formulas):

and another view, showing how the calculation is performed (just for the first and last row):

**Note: array formulas only work if the size of the two arrays match, in this case each one has 4 numbers, so each row multiplication can happen.**

Finally, we simply include the SUM function to add the four numbers:

`=ArrayFormula(SUM(B2:B5 * C2:C5))`

as follows:

**Quick Aside:**

This calculation could also be done with the SUMPRODUCT formula, which takes array values as inputs, multiplies them and adds them together:

`=SUMPRODUCT(B2:B5 , C2:C5)`

## Another Array Formula Example

In this example, we enter a single formula to multiple an array of row headings against an array of column headings. Again, this only works because the two arrays are the same size:

Hi Can you use arrayformulas wth text and numbers? I have a spreadsheet that then goes on to create a look up table and the I have copied down the formula =C3&A3 to create, for example VAN1 however I inserts rows into the sheet and I am trying to find a solution to automatically copy the formula. I think it should be an array formula however having spent 3 hours searching for a solution i’m, struggling – here is the sheet https://docs.google.com/spreadsheets/d/1HJFGX_4vhcGMefmO0fhDFZHnFcH6APdr_3-M7YNHgz4/edit?usp=sharing

Hey Mike,

Looks like you’ve got the array formula working now? You should be able to do it with something like this:

`=ArrayFormula(C:C&B:B)`

Also, you should change the permissions on your sheet to View-only or turn sharing off again, in case anyone else clicks that link.

Cheers,

Ben

OR & AND functions do not work inside the otherwise awesome ARRAYFORMULA. Is there an easy way to substitute OR & AND inside the ARRAYFORMULA?

What I did to overcome this with the AND() formula was to substitute it by a multiplication (*), so:

=AND(TRUE, TRUE)

is equivalent to writing:

=TRUE * TRUE

Great tip! Thanks for sharing Pedro ðŸ™‚

I have a google spreadsheet where I have the first row as (for example):

`=googlefinance("AAPL","price",today()-60,today())`

The historical data actually only returns 60 days of data up to yesterday, and the number of rows varies with weekends/holidays.

So I manually have to place the following at the next empty row following whatever that above formula returns to return the current price for today:

`=googlefinance("aapl")`

Since the number of rows returned for the first formula varies, I have to delete the last one, let the first formula populate rows, and then place the last formula on the next empty row.

Can I use arrayformula (or something else) to basically append the last formula to the result set of the first formula?

Hey Mark,

This formula should do the trick for you:

`={googlefinance("AAPL","price",today()-60,today());{today(),googlefinance("AAPL")}}`

which uses an array formula to append the current result to the historic one.

Hope that helps.

Cheers,

Ben

What arrayformula do I use if I want to use the value in A and multiply by diff amounts. For example : A multiplied by 4 for row B, A multiplied by .5 for row C, etc.

What would be the syntax if I wanted to use an array formula for the summation of 3 columns together?

You can use a standard SUM formula to sum 3 columns together, e.g. this will add all of A, B and C together:

`=sum(A:A,B:B,C:C)`

I have this with 10 columns. Of course I could write

=sum(A:A,B:B, …., J:J)

but that is a bit tedious. In a non-arrayformula I would write

=sum(A1:J1)

and I need more of these on other column ranges. Is there a way to use array formulas without having to mention each column individually?

Hi Ben,

Love your work, really saves a ton of time. Thank you.

Here’s the prob: I have dates in the first column, and the second column is an index value, say {A,B,C}, so each date has three index corresponding to them, and the remaining 10-15 columns contain information, e.g. shirt size, color, cost, price, source, etc.

How can I find the color (column 4) by matching the date and the index value, say “B”?

This is a typical database-style query, but I don’t want to make a database for one spreadsheet.

Thanks!!

Hi Kevin,

Not exactly sure what you’re after, but it sounds like you might need a lookup based on multiple conditions. Have a look at this post from Chandoo’s site: https://chandoo.org/wp/2010/11/02/multi-condition-lookup/

Hope that helps!

Ben

I’m not sure if this is the proper use of Arrayformula but I have a sheet that I clear with a script (which wipes out all the formulas) and need to add back the formulas with another script. Am I headed in the right direction, or is this even enough info?

Hi Taylor,

Sounds like you can use Apps Script to create your new formulas, so it doesn’t sound like you’d need the ArrayFormula.

Ben

Hi Taylor,

Can you help me with the script which wipes out all the formulas and then after a defined time it adds back the formulas again.

Good evening, Ben.

I have looked all over and I can’t seem to find an easy solution to this issue of my linked Forms-to-Sheets Query function adding a pesky extra row. Because of this, my form (response) data never gets moved over (via the Query function) and calculated. Help!

So, I have the ‘Form Responses’ tabbed sheet and I have created a separate tab within that sheet that I call ‘Calculations’. In Calculations I am placing =ARRAYFORMULA(Query(‘Form Responses’!A2:AE2)) in cell A2. When the form is filled out and submitted, responses properly fill my A2:AE2 cells in ‘Form Responses’ but, when I jump over to ‘Calculations’, there is no data within the row. What is frustrating is that the A2:AE2 in my argument now says A3:AE3. The more responses I receive the higher the number; A3:AE3 becomes A4:AE4 and so forth. I have also just tried the Query command alone without the ARRAYFORMULA and I still run into this problem of Sheets wanting to add this invisible row. Is there a fix? If so, could you help me out?

Thanks! Frustrated Google Sheets User

Hi Patrick,

You shouldn’t need the ArrayFormula in this example. The query function deals in arrays already.

Try changing your query function to this:

`=QUERY ( A1:AE , "select *", 1)`

You need to have a select clause in there and also leave off the final row reference, like this A1:AE.

You can read more about the query function here: https://www.benlcollins.com/spreadsheets/google-sheets-query-sql/

Hope that helps!

Ben

I hope you can help. I am trying to use the copydown add on for a =vlookup command. I am using a separate sheet to lookup an email address when a form is completed with a specific school.

Is this possible to do with an Array Formula?

Yes, you can nest your vlookup inside an array formula, for example:

`=ArrayFormula(if(A1:A<>"",vlookup(A1:A,Sheet2!$A$1:$B$5,2,false),""))`

Hi, Ben! Is it possible to use ArrayFormula to fill a column with, in each cell, the sum from that cell to the bottom end of the column?

Many thanks!

Hello I have a =getStatusCode(D3) which I have to manually copy or drag down

is there a way I can add this code to the column automatically and that it updates ex. to D4, D5,D6 etc?

Here’s the code for the script editor:

Utilities.sleep(Math.random() * 1000);

function getStatusCode(url)

{

var url = encodeURI(url)

var options = {

‘muteHttpExceptions’: true,

‘followRedirects’: false

};

var response = UrlFetchApp.fetch(url, options);

return response.getResponseCode();

}

Maybe my later Comment on UDF’s on

September 7, 2018 at 12:53 pm

entitled

The issue described here may be of interest to those who use ARRAYFORMULAS and need to combine with UDFâ€™s.

…..

may be of use.

John

I’m trying to combine the fill down idea with the google finance function like this:

‘=ArrayFormula(if(K2:K=””,””,googlefinance(K2:K)))

column K is a dynamic ticker list that’s populated by a query, but this formula only finds the ticker in k2. How do I get it to find k3, k4, etc.?

Is it right that Arrayformula and the IF function don’t work together in google sheets?

I have a spreadsheet which displays the Current Month if the Current Month matches the Month of the date in Column A. If the dates in Column A do not match with Current Month, then i would like to display the Previous Month, but not 2 or more months. At a time, only 1 month should be present in the Current Month column (either Current Month OR Previous Month), otherwise it should be blank.

Here is the Google Spreadsheet.

https://docs.google.com/spreadsheets/d/1XU3dfsWYicyY_Rbeb1TCVO-h1ghbpz01ZKpesHioet4/edit?usp=sharing

for some reason, this ArrayFormula evaluates and shows both the Current as well as Previous months.

=ARRAYFORMULA(IF(A2:A = “”, “”, IF(TEXT(DATE(YEAR(B2:B),MONTH(B2:B),1),”mmm-yyyy”) = TEXT(DATE(YEAR(TODAY()),MONTH(TODAY()),1),”mmm-yyyy”), UPPER(TEXT(DATE(YEAR(TODAY()),MONTH(TODAY()),1),”mmm”)), IF(((TEXT(DATE(YEAR(TODAY()),MONTH(TODAY())-1,1),”mmm-yyyy”) = TEXT(DATE(YEAR(B2:B),MONTH(B2:B),1),”mmm-yyyy”))), UPPER(TEXT(DATE(YEAR(TODAY()), MONTH(TODAY())-1, 1),”mmm”)), “”))))

How can this formula be modified to list only either Current or Previous month?

Seems like this would be simple for what I am trying to do with the arrayformula function. I have an event form that the event coordinator takes down event information from customers. I need the column that shows what day of the week to parse the XX/XX/XX and the result would be “Saturday”. I have that formula working no problem but when an new entry is submitted it drops the row down. Thinking arrayformula would work but I cant get it to be consistent in the column.

Link to form:

https://docs.google.com/spreadsheets/d/1Z7G4ylT7TZsqduCIsvXDsM1iREwI3v-SsnHDupeSP1Y/edit?usp=sharing

Hi there, I am trying to create an Order Form. Some items are different prices and different quantities. Example

Item 1

Blue ($8) – 1 unit

Pink ($12) – 2 units

Will this formula work and can I display the amount in the form before submission?

Thank you!!

Hi, Ben!

How could I run this with ArrayFormula. I’ve read I have to replace “OR” with “+” but I can’t really wrap my head over it. Thank you!

=IF((or(B51=”Austria”, B51=”Finland”, B51=”France”, B51=”Germany”)),”EUR”,if(B51=”Denmark”, “DKK”, if(B51=”Norway”, “NOK”, if(B51=”Sweden”, “SEK”, if(B51=”United Arab Emirates”, “AED”, if(B51=”United Kingdom”, “GBP”, if(B51=”United States”, “USD”,”NA”)))))))

In reply to Alex. Look at my comment in the original article on using OR or + in a FILTER function which can usually be avoided.

There are also much better ways of handling multiple OR’s.

See here for a way of avoiding + by using MATCH in a situation where matching is actually the task!

https://docs.google.com/spreadsheets/d/1gKJPJeN7DHNfSO1F5Qv3u1pf9PY484ceEIiQr5MVdeM/edit?usp=sharing

The issue described here may be of interest to those who use ARRAYFORMULAS and need to combine with UDF’s.

An issue with user defined functions (UDF’s) and indeed some inbuilt sheet functions such as CELL when used with ARRAYFORMULA is that they don’t deal with array ranges properly or as you would like is probably more accurate to say.

For example “=ARRAYFORMULA(CELL(“address”,A1:C1))) only provides a 1-element array $A$1 rather than the array [$A$1,$A$2,$A$3] which is presumably what might be desired. The reason being that such functions are not designed to work with arrays.

I needed a function which would automatically provide the column labels A,B etc. as an array for a range rather than just column numbers. For example I wanted to use something like “=TRANSPOSE(ARRAYFORMULA(colLabel(COLUMN(someRange))))” where colLabel is a UDF which returns the column label given the column number. This unfortunately doesn’t work as also CELL doesn’t unless you make provision for dealing with an array of values rather than a single value passed to your UDF.

I couldn’t find anything which deals with this issue simply so I came up with the following which should also indicate how to proceed whenever you need to provide a UDF which can be used with ARRAYFORMULA.

function colLabel(rngValues){

// Function which processes the individual cell values passed to the UDF

function label(a){

var temp, l = ”;

while (a > 0)

{

temp = (a – 1) % 26;

l = String.fromCharCode(temp + 65) + l;

a = (a – temp – 1) / 26;

}

return l;

}

// Basic handler for the passed 2D range values

// Process the array values with a function like “label” to suit your requirements

// rngValues is a Javascript 2D array

for(var j=0;j<rngValues.length;j++){

// r is row j of the array rngValues which contain the row values in array r=rngValues[j]

var r=rngValues[j]

for(i=0;i<r.length;i++){

// Press the row values and pass the new values back

r[i]=label(r[i]) }

}

// Return the processed array

return rngValues

}

HTH.