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))`

## Can I see the example worksheet?

## 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

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!!

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?

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