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