How do array formulas work in Google Sheets?

Array formulas

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:

Array formulas example data

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?

Yes, here you go.

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:

Array error message

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

Array formula explained

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

Array formula explained

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:

Array formula

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:

Array formula

5 thoughts on “How do array formulas work in Google Sheets?”

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

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

  2. 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?

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

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

Leave a Reply

Your email address will not be published. Required fields are marked *