Have you ever tried to use a formula in a column adjacent to your form responses to do calculations? You’ve copied it to the bottom of your sheet, maybe even included an IF statement for the blank rows, and now you want it to auto-calculate whenever new responses come in.

Sadly, this approach doesn’t work.

When a response is collected through the form it adds a new row under your existing data, and any formulas in adjacent columns get bumped down a row rather than being calculated. Bummer!

However, this is a perfect use case for an Array Formula (never heard of these before? Start here).

In the following example, I’ve set up a simple Google Form which asks a user to submit a single number between 1 and 100. The form responses are collected in columns A and B of a Google Sheet (timestamp and number respectively).

The other columns contain array formulas to calculate various metrics e.g. running totals, %, average etc. (all made-up for the purposes of this example).

With array formulas, we create a single formula in the top row of Sheet, which will automatically perform calculations on any new rows of response data from the Google Form.

**Note:** in general, and especially if your forms are complex, you should consider keeping the response data in its own sheet, and doing any data analysis in a separate sheet.

# How to use Array Formulas to fill the entire column when working with Google Forms

## What’s the formula?

**Array Cumulative SUM**: To get the total of all values in column B, enter this formula in the top row (e.g. cell C2):

`=ArrayFormula(IF(ISBLANK($B$2:$B),"",SUM($B$2:$B)))`

**Array % of TOTAL**: To calculate the % of values in column B, enter this formula in the top row (e.g. cell D2):

`=ArrayFormula(IF(ISBLANK($B$2:$B),"",$B$2:$B/SUM($B$2:$B))`

**Array Average**: To calculate the average of all values in column B, enter this formula in the top row (e.g. cell E2):

`=ArrayFormula(IF(ISBLANK($B$2:$B),"",AVERAGE($B$2:$B)))`

**Array IF**: To create categories for values in column B, enter this formula in the top row (e.g. cell F2):

`=ArrayFormula(IF(ISBLANK($B$2:$B),"",IF($B$2:$B > 50,"Large","Small")))`

All of these will expand to fill out the entire column, displaying values for any rows that have numbers in column B. They will auto-update when new data arrives through the Google Form.

## Can I see an example worksheet?

Here’s the link to the Goolge Form so you can see the formulas auto-update.

## How does this formula work?

Let’s run through how the first of these array formula examples, the SUM example, works.

The way to think of it is that in the first row, we effectively have this formula:

`=IF(ISBLANK(B2),"",SUM($B$2:$B))`

This regular formula checks if cell B2 is blank or not.

If it’s blank then the ** ISBLANK** formula returns

**and our IF formula outputs**

`TRUE`

**in cell C2, in other words a blank cell.**

`""`

However, if cell B2 has a number in it (from the Form), then we put the total of column B into cell C2. The syntax ** SUM(B2:B)** ensures that we include

*ALL*numbers in column B into our total calculation.

Now consider the next row, where our formula effectively becomes:

`=IF(ISBLANK(B3),"",SUM($B$2:$B))`

It’s identical except we’re checking row 3, so whether ** B3** is blank or not, and outputting the

**result into cell**

`IF`

**.**

`C3`

Finally, we turn it into an array formula by putting a range into the ** IF ISBLANK** test, and wrapping with the ArrayFormula syntax:

`=ArrayFormula(IF(ISBLANK($B$2:$B),"",SUM($B$2:$B)))`

You only enter this formula once, into cell ** C2** (or whatever your top row is) and it will auto-fill the whole column.

Whenever a form response is added to the Sheet, a new number appears in column B and that cell is no longer blank. Hence the array formula updates to display the ** SUM** value into the adjacent cell in column C.

Should the arrayformula not be:-

`=ArrayFormula(IF(ISBLANK($B2:$B),"",SUM($B$2:$B)))`

ie, no $ before the first 2 so that Google knows which parts to increment – otherwise, why would it increment the $B$2 in the ISBLANK but not the $B$2 in the SUM, thereby producing on the second line the equivalent of:-

`=IF(ISBLANK($B3),"",SUM($B3:$B))`

Hey Nick,

Great question! Actually both work in this example, with or without that $ before the first 2. Another way to think of it is that the

`ISBLANK($B$2:$B)`

syntax produces an array of TRUE/FASE values`{TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, etc....}`

which the IF formula uses to decide whether to display the SUM.Cheers, Ben

Thank you for sharing, really appreciate this as it will help my A level students in further understanding of array formulas.

Great examples.

=ArrayFormula(if(row(I:I)=1,”status”,iferror(vlookup(A:A,’Sheet ‘!A2:J,8,false))))

I like the above formula because it put the title in row one.

I also like to delete the empty rows in the response sheet.

Maybe this is a little too complicated.

Nice! Thanks for sharing Joe.

Excellent article!

I was wondering how could I make it work when nesting the “filter” formula.

Let’s see this with a non-functional example:

I have a spreadsheet with 3 columns; “name”, “last name” and “age” (with a list of unique entries). In another tab, entering to the first two parameters via form (“name” in column A and “last name” in column B) I would like the system to populate the corresponding “age” value retrieved from the first table. The formulae I’m trying to use for this case scenario is as follows:

`=arrayformula(if(isblank(A:A);;filter(table!$C:$C ; A:A=table!$A:$A ; B:B=table!$B:$B)))`

Any idea how to make it work?

Thanks in advance

Hey Joseba,

Sounds like the function you want to use is VLOOKUP, which would look like this in your array example:

`=ArrayFormula(if(isblank(A:A),"",vlookup(A:A&B:B,{table!A:A&table!B:B,table!C:C},2,false)))`

You may need to change the commas , to semi-colons ; if you’re based in Europe…

Hope that helps.

Ben

Hi Ben,

It perfectly works with your solution đź™‚ Thanks so much for your support!

Joseba

Hi,

I am a school administrator attempting to go from a form which fills in a sheet which I will then use to send home progress reports using YAMM.

Form Link: https://docs.google.com/forms/d/e/1FAIpQLSeeB1ZcjK4fi8jJnIaMNXekWtWEtwp6on6EERD6mbSZijpFxg/viewform?usp=sf_link

Spreadsheet results link: https://docs.google.com/a/homeoftheshamrocks.org/spreadsheets/d/1iGv4wPnsy6-i6OkUCD7oFQZQG5BK-0P66zs-apbjvlg/edit?usp=sharing

I want the sheet to pull the email addresses from page two which is my email map. Your thoughts to fix my formula would be MOST helpful!!

Requested access to view your sheet.

Buenas tardes, necesito Ayuda.

Se trata de una hoja de cĂˇlculo, â€śRESUMENâ€ť con datos importados de varias hojas. Para ello uso la siguiente formula:

=ArrayFormula(QUERY({

Septiembre2017!C12:C\Septiembre2017!D12:D\Septiembre2017!AK12:AK\Septiembre2017!E12:N\Septiembre2017!Q12:Q\Septiembre2017!T12:U\ISNUMBER(Septiembre2017!AK12:AK)

};”select

* where Col17 = TRUE”))

Obviamente al importar los datos de la columna â€śAKâ€ť, solo me importa los valores numĂ©ricos, eliminando la fila, cuando en la columna â€śAKâ€ť encuentra una celda que contenga texto (incluso si es nĂşmeros y texto). Lo que necesito es que solo me suprima la fila si el texto, de cualquier celda de la columna â€śAKâ€ť, es una palabra concreta, como por ejemplo â€śPRUEBAâ€ť

Gracias

Hi, I need help.

It is a spreadsheet, “SUMMARY” with data imported from several sheets. For this I use the following formula:

= ArrayFormula (QUERY ({

September2017! C12: C \ September2017! D12: D \ September2017! AK12: AK \ September2017! E12: N \ September2017! Q12: Q \ September2017! T12: U \ ISNUMBER (September2017! AK12: AK)

};”select

* where Col17 = TRUE “))

Obviously when importing the data from the column “AK”, I only care about the numerical values, eliminating the row, when in the column “AK” it finds a cell that contains text (even if it is numbers and text). What I need is that I only delete the row if the text, of any column in the column “AK”, is a specific word, such as “PROOF”

Thank you

Hello – I am trying to add multiple columns (E thru Z) and have totals appear in column E. I cannot seem to get it to work. Should I be using query?

Thanks in advance.

Jill

How can I use array formula with this formula:

= IF(G3=’Summary Lookup 3′!$B$17,MAX($A$2:A2)+1000,0)