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)

Hey Aldo, can you share the sheet (or a copy) otherwise it’s hard to figure out how this would work?

Cheers,

Ben

Ben,

Yet another great article.

I am trying to convert a standard formula to an array formula and it does not seem to be working. The formula uses a user defined formula which I hope is not the issue.

Here is the standard formula:

=isautofail(L$1:AF$1,L2:AF2,AutoFailQuestionsTable)

And here is my last attempt at the array version:

=ArrayFormula(IF(ISBLANK($L$2:$L),””,isautofail($L$1:$AF$1,$L2:$AF,AutoFailQuestionsTable)))

The original file is on my corporate G Suite and could not share unless on our domain, so I downloaded as xls and uploaded to my personal drive, I think I fixed any issues during the conversion process.

Link:

(removed by moderator)

The formula is the last column on the “Recruiter Responses Formatted” sheet.

Any suggestions would be greatly appreciated!

Regards,

John

I also tried converting a standard formula to an array formula on another sheet in the same spreadsheet and also had no luck:

Standard Formula:

=VLOOKUP(A2, RecruiterResponses,4, false )

Array Attempt:

=ArrayFormula(IF(ISBLANK($A$2:$A),””,VLOOKUP($A2, RecruiterResponses,4, false )))

BTW, I tried every combination of absolute and relative references to no avail. 🙁

ok, was able to get one of my formulas working as an array:

=ArrayFormula(IF(ISBLANK(A2:A),””,VLOOKUP(A2:A, RecruiterResponses,4, false )))

But still having trouble with the one that uses the user-define function IsAutoFail()

Hey John,

Couple of ideas here (although I’ve never used a custom formula in an array formula before):

– the code for your apps script must accept an array as an input, if you’re intending to use it in an array formula.

– you can set the custom formula to output to a whole range of cells (E.g. column) in the apps script code, rather than use an array formula in Sheets.

Have a look at this: https://developers.google.com/apps-script/guides/sheets/functions#optimization

and this: https://stackoverflow.com/questions/27002683/applying-a-custom-function-to-an-entire-formula-without-arrayformula

Hope that helps!

Hi Ben,

Thank you so much for getting back to me.

I actually have read those articles and perhaps a dozen or so more. 🙂

The function does takes a range which from my understanding is nothing more than an array as it also looks like in the debugger.

I got arrayformulas working for vlookups and other standard functions like INT(), neither of those typically take an array as an argument but somehow behind the scenes google seems to call the function one by one substituting the appropriate values from the current row.

Regards,

John

So I bit the bullet and re-worked my UDF so it returns an array and seems to work, but….

Now I have another issue, the UDF does not recalc if data in the row changes nor does it add a row when a new row is added.

From what I can tell this is a caching “feature”. Suggestions on a work-around would be appreciated.

Found a work-around but not really digging it.

https://stackoverflow.com/questions/17341399/refresh-data-retrieved-by-a-custom-function-in-google-spreadsheet/17347290#17347290

By adding a “dummy” parameter to the UDF when the value changes of that parameter it recalcs as would expect. However this is a kludge and requires a dummy cell and a menu choice for the user to select a “refresh” option.

Well I believe I found a better solution that does not require a dummy cell although it does require a dummy parameter. I wrapped my original UDF in another function to return an array and discovered it does not update if data in the row changed or a row was added via a new form submission.

So I played with wrapping the new formula as an array formula and added a dummy parameter at the end and it seems to work, go figure!

Here is the formula:

=arrayformula(GetAllAutoFails(2, “L”, “AF”, $L$1:$AF$1, AutoFailQuestionsTable,L2:AF))

The last parameter L2:AF is the dummy parameter FWIW.

Good work! Great to hear you’ve got it working. Thanks for sharing your findings here, so it may help others. I’ll take a deeper look when I have some more time.

Cheers,

Ben

Thanks Ben!

Well although it worked on the copy of the spreadsheet I had, when I went to update the production spreadsheet I got the following error:

Error You do not have permissions to access the requested document. (line 31).

Line 31 was a call to the GetValue() method. Very strange as I have edit access to the spreadsheet. My company has a team drive, my department has a team drive and of course I have my own drive. The spreadsheet on the company drive shows owner as teleNetwork, the one in my department drive shows owner is Recruiting. I am guess this has something to do with the error as the copy on either Recruiting or my drive worked just fine.

My research was taking me no where so I decided to rework the function so as not to call GetValue().

Despite the extra pain endured it worked out better as the new function takes less parameters and none of them are bogus. It also works as an array formula w/o using {} or ARRAYFORMULA().

Example usage:

=GetAllAutoFails(L2:AF, $L$1:$AF$1, AutoFailQuestionsTable)

Thanks for all your ideas and great articles.

John

Great forum, I think an ArrayFormula might be what I need perhaps in combination with a query, lookup, or filter. Hopefully you can help!

I want to produce a two columns that contain the unique possible dates and employees who were hired prior to that date from two a sheet that has a list of employees and hire dates and two fields that specify the start and end date.

Date Range sheet:

Start Date: 1/1/2017

End Date: 1/3/2017

Employees sheet (name, hire date)

Fred – 1/1/2017

Steven – 1/2/2017

Bob – 1/3/2017

Desired Result

1/1/2017 – Fred

1/1/2017 – Steven

1/2/2017 – Fred

1/2/2017 – Steven

1/2/2017 – Bob

1/3/2017 – Fred

1/3/2017 – Steven

1/3/2017 – Bob

Dan,

Without seeing your data, seems like a basic query would work just fine. You will want to review Ben’s article:

Filtering with dates in the QUERY function

https://www.benlcollins.com/spreadsheets/query-dates/

As using dates in the WHERE clause is a bit different than standard SQL.

Pivot tables also comes to mind and there are also several other options as well.

Regards,

John

Thanks for the examples. I’ve worked through those as well as some of the previous questions and am still not sure how to approach my issues.

1) I would like the arrayformula to sum the current row, not an entire range. So, for example, my base formula is: ABS(sum(F2-E2). When this nested into the arrayformula, =ArrayFormula(if(ISBLANK($B$2:$B),””,IF(ISBLANK($E2), “”, ABS(sum(F2-E2))))), it always returns the sum for row 2 on all rows.

2) If there is a solution to that, I can probably apply it to my next one. I would like to SUMIFS the results of that calculation based on the values in columns B and C for the current row. This formula, entered manually and filled down, works as I want it to: =SUMIFS($H$2:H, $B$2:B, “=”&B2, $C$2:C, “=”&C2)

But if I apply ArrayFormula to it, it seems ‘hard coded’ to return only what would apply to row 2.

Thanks!

Bruce

Hi Bruce,

Looks like you are in same boat as I was as we both had to perform operations on values across the row. Perhaps Ben or others have another idea, but you can get the desired results by writing a user-defined function (UDF) that returns an array. I wrote a quickie that seems to work but left off the checks for ISBLANK() in the call.

Put this formula after your last column and copy the code into your script editor.

=ArrayFormula(Bob(E2:F))

You can add your ISBLANK() wrappers, just make sure you get the ranges correct ex. ISBLANK(E2:E)

Regarding my example below, when I wrote my UDF I called the getValues() method and worked fine both in the debugger and calling it normally, both on my google drive and my department’s google team drive (drive owner shows as my department).

However, in the production spreadsheet which is located on some other team drive (drive owner shows as my company), the UDF threw an error:

getValue() Error You do not have permissions to access the requested document.

I investigated a bit and felt it was better to switch my algorithm rather than spin my wheels trying to figure out why getValue() was giving me fits. Also, the people responsible for the production spreadsheet were not interested in moving it to another team drive.

So depending on your situation you may not have the same issue and can call the getValue() method w/o throwing an error.

Anyway, here is the UDF:

function Bob(RangeAnswers) {

var nCurrentElement = 0;

var anSum = [];

// uncomment 2 lines below to use the debugger while on the correct sheet

// var ss = SpreadsheetApp.getActiveSheet();

// var RangeAnswers = ss.getRange(“E2:F”).getValues();

while (RangeAnswers[nCurrentElement][0] !== “”){

anSum.push(Math.abs(RangeAnswers[nCurrentElement][0] – RangeAnswers[nCurrentElement][1]));

nCurrentElement++;

}

return anSum;

}

Regards,

John

A couple of other notes:

– When I was getting the getValue() Error on the company team drive I did not get the error while running in the debugger which further confused me.

– I named the UDF Bob(), you can and should change the name, I realized your name was Bruce after I posted, not that Bruce() is a good name either. 🙂

Regards,

John

Not sure why my thank you reply is not showing up, but thank you. 🙂 This worked perfectly. I’d not thought of writing a custom function so this is another arrow in the proverbial quiver!

And I think Bruce() is a super function name (though I did use something more appropriate). 😉

Bravo for the Bruce() and Bob() functions!

Thanks for helping out John and great to hear this sorted for you Bruce.

Cheers both,

Ben

One more update, I was not thinking clearly when I wrote how to call the UDF. As with the one I wrote for myself, since the UDF accepts an array and returns an array there is no need to use ARRAYFORMULAm so you can call the UDF simply like any other function:

=Bob(E2:F)

Thanks for your article Ben. These array stuff seems to me like reading Greek! LOL. But I do need help with my own peculiar issue. I have a form that requires input on attendance records like No of Adult Male, No of Adult Female, No of Male Teens, No of Female Teens, No of Children. Is there a way to have the summation of these values automatically? Either live on the form or on submission on the results spreadsheet?

Thank you in advance!

You could insert a row above the form responses that sums up each column with a formula like this, e.g. for column B:

`=SUM(B4:B)`

assuming that your values start in row 4. You leave the last B reference with a row number, so it will always include everything to the bottom of the column, even when new stuff gets added.

Hope that helps!

Hello. Hope someone can assist me. I’m trying to get the following formula to autofill down with an Arrayformula, but having no luck.

`=if(Or(G2>F2,G2=""),0,1)`

Column G & F are date columns.

Any help would be greatly appreciated.

Hey Valerie,

You need to use ranges in your ArrayFormula. Also, boolean logic (AND or OR) does not seem to work with the array, it always just gives me back the first value.

However, you can use this formula in your first cell, which will copy down your column:

`= ArrayFormula(if(G2:G="", 0, if(F2:F>G2:G, 0, 1)))`

You may wish to change it to this, so you don’t have 0’s all the way down your column:

`= ArrayFormula(if(G2:G="", "", if(F2:F>G2:G, 0, 1)))`

Thanks,

Ben

Ben,

Thank you for your help. Before I saw your post, I came up with a solution thanks to your Advanced Formulas 30 Day Challenge (which by the way is one of the best tutorials I’ve seen).

My solutions isn’t as pretty as yours, but it gives me the correct answer and doesn’t fill in if Column A is blank.

Below is the solution that I came up with.

=ArrayFormula(IF(ISBLANK($A$2:$A),””,(ArrayFormula(–($F$2:$F>=$G$2:$G)*ArrayFormula(–($G$2:$G””))))))

Thanks again!

Hi Ben,

I use the following formula to calculate a position in class

=SUMPRODUCT((F3=$F$3:$F$53)*(I3>$I$3:$I$53))+1

Col F is the Class Name and Col I is the Total. It works well but I am trying to use ArrayFormula to autofill like so

=ArrayFormula(If(C3:C = “”,””,SUMPRODUCT((F3=$F$3:$F$53)*(I3>$I$3:$I$53)))+1)

Instead of getting a position in class I now get the answer 1 for each row

Thank you

Hi all,

I have an array formula that I am trying to start in the 2nd row, but it gives me an error everytime I copy and paste it to anything other than row 1.

Any idea how to run this on the second row?

Thanks!

=ARRAYFORMULA(IF(ROW(A:A)=1,”Insert_Column_Heading”,IF(A:A=0,”No Lookup Value Provided”,IFERROR(VLOOKUP(A:A,CHOOSE({1,2},{B:C}),2,0),”Need Value”))))

Hi Kyle,

Try changing all of your ranges from A:A or B:C to A2:A or B2:C with a reference to row 2. That should do it.

Cheers,

Ben