In this article, you’ll see how to use Array Formulas with Google Forms data to automatically calculate running metrics on your data.

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? Check out: How do array formulas work in Google Sheets?).

In the example above, 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 with Google Forms data to calculate various metrics e.g. running totals, %, average etc. (all made-up for the purposes of this example).

Using Array Formulas with Google Forms data, 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.

Learn more about working with Advanced Formulas in the Advanced Formulas in Google Sheets course

## How to use Array Formulas with Google Forms data

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

can you please help me, I need to either, populate a field on my google form based on the selection of a different dropdown field , or have an array formula on my sheet to lookup the item on my orders sheet and return the item order number. but there is several orders for the same item.

for example:

ITEM ORDER#

1 11122

2 22298

1 11125

IF I pick item 1 which has 2 order numbers how do i make sure to pick the right one?

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

hey Ben

i have problem with my arrayformulas need big help from you

thiss a short ex: of the fmulas: =ARRAYFORMULA(S9+B9)

but is not working at all it just prompt #value then the error message is: Error

Function ADD parameter 2 expects number values. But ‘Jeff’ is a text and cannot be coerced to a number.

i will love to hear from you thank you

same problem

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.

Hi Rachel,

May I get a copy of your form and sheet please.

Thank you

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

Arrayformula and Query donât work together

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

Is there a way to auto update a cell on one sheet dependent on the outcome of another sheet? even if other data will be added into the same column?

For example,

I have one sheet where information will be inputted, where the information needs to be copied onto another sheet, however other information can also be added to this second sheet column. Is there a way for the information to be added to the second sheet as a continuation of other data that may have been added independent of the first sheet?

If it helps, both sheets have drop down lists with the same options.

I’m new to Array Formulas — the one I need is this:

Form User enters a date for an event. This goes in column J on my Google Sheet.

I want to have the adjacent cell (in column K) automatically populate with a date that is 7 days earlier than what the form user entered.

Can you help me with that?

Hi Ben,

I am using array formula with the IF, AND and Date functions. If I don’t use the array formula, I am getting the right result. If I use the array formula the result is not right and I am not sure why this is happening.

Value in cell X3 = 02/09/2013

Formula in cell AL3 is:

=arrayformula(if(and(X3:X>=date(2013,4,1), X3:X>=date(2014, 3, 31)), “Yes”, “No”))

The result I am getting is a “No” whereas the date is clearly the 2nd of September 2013 and falls between the 01 April 2013 and 31 March 2014.

If I use the same formula without the arrayformula function then I get a “Yes”. Any ideas why this is the case please?

Koks geras straipsnis!

Thanks! Or should that be “AÄiĆ«” according to Google Translate.

I have a Google Sheet that is auto populated from the greatest survey tool I have ever used, SurveyGizmo

My dilemma is that I would like a simple formula to autofill and calculate each time a new row is added and I donât know formulas or the formatting for ArrayFormulaâs that well

When I tried placing a formula in my second row, something went very bad and my Google Sheet that had about 27 rows of data starting auto-creating blank rows and locked up until it created about 3500 blank rows!

Can you please help me and let me know the charge (which I am HAPPY to pay) for your time?

Formula I tried to piece together from what I read online I thought should be =ArrayFormula(If(row(AD:AD)=1,”AY Cost”,SUM(L2+M2+N2+O2+W2+AA2-AH2)))

Where I thought If(row(AD:AD)=1 was correct to be on the look out for new rows

I thought maybe my mistake was placing this on Row 2 when there was already 27 existing rows of data and, in hindsight, maybe I should have placed the formula on the LAST existing row that has data? But I am now afraid to do anything.

Can you help me understand what is wrong here?

Hi Ben,

I hope you can assist me with this. Iâm trying to get the following formula to autofill down with an ArrayFormula, but the formula only copies the result for row 2 onto the rest of the rows.

=JOIN(” – “,A2:D2)

where columns A to D are all text columns.

I’ve changed it to the ArrayFormula setup you’ve suggested, but still nothing changes:

=ARRAYFORMULA(IF(A2:A=””,””,JOIN(” – “,A2:D2)))

Any help would be greatly appreciated.

The array formula has to be posted at row 1 of a column. There must be no pre-populated data in a row underneath an array formula.

Hey there,

This is really helpful, but i’m trying to use a different one that is not working, the last post seems to have the same issue. My formula is:

`=arrayformula(if(isblank($A$3:$A3),"",((countif($O3:$AB3,"Skill")*1)+(countif($O3:$AB,"Developing")*0.5))/14))`

when a new form is added, the score remains the same. It is not calculating for the new data. What would be the best way to have it resolved?

Thanks!

Sorry,

This is the correct formula that is not working:

`=arrayformula(if(isblank($A$3:$A),"",((countif($O3:$AB3,"Skill")*1)+(countif($O3:$AB3,"Developing")*0.5))/14))`

I’ve been struggling with a form that I set up that has both number values and yes/no answers. I want to assign a y/n a numerical value: =IF(J2=”yes”,5,IF(J2=”no”,0))+IF(Q2=”yes”,5,IF(Q2=”no”,0)), as well as add the number values from the other ten columns so that I can get a total sum (score) for each form that is filled in.

Greetings,

I was able to work out my formula:

=ArrayFormula(IF(J2=”yes”,5,IF(J2=”no”,0))+IF(Q2=”yes”,5,IF(Q2=”no”,0))+SUM((F2*2)+(G2*2)+(H2*4)+I2+(K2*2)+L2+M2+N2+O2+P2+S2+T2))

However, when I paste into a blank column in my sheet attached to my form it still does not function, and the next entry continues to show as blank. I’ve copied into the first row as in your example, but I can’t seem to get it to function like your sheet.

How would the function that automatically takes the sum of the two previous columns and puts it into the third column? In other words, I want the sum of B2 and C2 to populate D2 and the sum of B3 and C3 to populate D3 without having to put in a unique formula for each row.

Thank you

Hi Shania,

Try this formula:

`=ArrayFormula(if(B2:B<>"",B2:B+C2:C,""))`

Hope that helps!

Ben

Hi Ben,

I’m looking for something similar, except I am trying to average several cells together i.e.: B2,E2,H2, and K2 and put these averages in let’s say cell N2. I want to do this for every subsequent cell below. Is there a way to do this?

Hi Ben,

I’m trying to combine countif and arrayformula but the result shows numerous rows of the same number. The countifs serve as a condition to check whether a number of cell has data then interprets it to a certain value. Can you help me out? Thank you.

Hi Ben,

I’m trying to look for a formula that would make a keyword in a phrase in the A column automatically show up in the B column. For example if I have the list below in Colum A:

John Smith

Robert Adilla

Bob Adams

Jay Smith

How do I get it so that every name with Smith in it has Smith pop up in Colum B. Im trying to organize large amounts of data. Do you have any suggestions? Thanks!

Is there any way to do an Array Formula which calculates across a number of sheets, by only entering it in one cell, as per this example? Also, my formula tracks across, not down.

Hi every body!

I hope you can help me correct my formula, I’m trying it to create an Array Formula on “ColumnI” that will provide a result if ANY of the following criteria matches:

1) If ColumnA is blank and ColumnB is not then SUM range ColumnD to ColumnH

2) If ColumnA is not blank and ColumnB is then SUM range ColumnD to ColumnH

NOTE: The data in Columns D to H, have already an Array Formula with a VLOOKUP that will provide a result from sheet1 if it matches and will also prevent any empty cell to populate unwanted data (N/A):

=ArrayFormula(IF(NOT(ISBLANK(D2:D)), VLOOKUP(D2:D,Sheet1!A2:B38,2,FALSE),))

So with that said, I have the following formula working in ColumnI:

=ArrayFormula(IF(OR(A2:A=””,B2:B=””),””,AQ2:AQ+AR2:AR+AS2:AS+AT2:AT+AU2:AU))

However, I do get data on further cells that have not been captured from my live google form, I get 0’s (zeros). What will I have to change to prevent the 0’s?

Please help! Muchas gracias!!

Hi

I’m having a problem using Averageif with arrayformula. Do you have a tip?

I have numbers in the rows, G to P, and want the average of that particular row.

=ArrayFormula(IF(ISBLANK(B2:B),””,AVERAGEIF(G2:P2,”0″)))

It works like a charm with checking row B, and only populates expected cells in its row, leaving the rest blank, but it takes the average of the first row and posts it in every cell instead of calculating the average of every row.

Hi Ben and Community,

Hopefully someone has insight into what would make this work or not. My ARRAYFORMULA isn’t populating new response rows.

So for some reason when I first did this, it populated down the rows just fine. Then when I tried to tweak the formula to remove an unnecessary IFERROR statement, it’s refusing to populate new rows, even when I return it to the original formula.

The formula functions properly, returning either #N/A or the desired response, but only in the cell where I explicitly place it or copy it to.

My formula is =ARRAYFORMULA(IF(ISBLANK(B2),””,IF(B2=VLOOKUP(B2,Closings!B:C,1,FALSE),”Closed”,””)))

My sheet is https://docs.google.com/spreadsheets/d/1JtMksOBbmZXBJkoa9XmWF8lvYuCcc4d2GIpQkvAEkps/edit?usp=sharing

The formula in question is on “Form Responses 1”, column G.

Hi there I am tearing my hair out over what I think is a simple task.

I have eight columns auto-populating with data if another column contains data. I’m using arrays and filling blank cells with ” ”

Now in column nine I want text to appear if any rows in those eight columns contain data, the data is a string (not number) If all the columns in the row are blank I want the ninth column to return “Nothing to report” and if one or more of the rows in the eight columns are filled in then it will simply be blank. Simply cannot work it out. I need an array as I’m using google sheets.

Hello hope someone can assist

hi Ben,

I would like to populate some cells in a bond calculator with information from a different worksheet(A) then after it calculates the market value it populates the resulting market value in a cell in worksheet A. all this to be done using one formula in this cell where output is to be displayed.

Kindly let me know if the arrays formula can work.

Hi I have a simple Google form that posts shifts to a sheet and I want to analyse the shifts in GDS.

I understand from your comment : “you should consider keeping the response data in its own sheet, and doing any data analysis in a separate sheet”

That makes sense but what is the best way to copy this data to a new sheet so it autoupdates each time. Should I also do calculations in the sheets rather than GDS?

Thanks for reading.

Hi,

Is there a way ARRAYFORMULA could be applied for populating the entire row? I am having issues with the referencing of range probably.

e.g. I have a table with 3 columns, wherein the data lies in A3:C10. What I wanted was to count the non-blanks in each column and display it in A1 through C1.

I am using the following in A1:

=arrayformula(if(COLUMN(2:2)””,counta(A3:A),””))

This works, however each column (A1, B1, C1…) is populated with just the value obtained from COUNTA(A3:A) and not COUNTA(B3:B), COUNTA(C3:C) and so on.

Is there a way it could be achieved. I would really appreciate any help with this.

Thanks

Just wanna drop a thank you. Instruction and examples worked great for what I needed.

Firstly, great article and thanks, this solved a few of my issues.

In my quest for ultimate laziness, is there any way to do this with DSTDEVP?

I tried the following but it doesn’t seem to work

=ArrayFormula(If(isblank($E2:$E),””,DSTDEVP($A:$E,5, {“EventKey”;$C2:$C})))

Basically the spreadsheet is such that every row is a record in my dataset and I want to calculate the stddev, mean and z score for every record. Columns A+B create a key which I use to group entries together and I store that key in Column C. Column D is the name of the participant and E is the raw score of the participant in a given contest. So the formula without the above attempt at modification is:

=DSTDEVP($A:$E,5, {“EventKey”;$C2})

Which calculates the stddev for a given event key which defines the population for DSTDDEVP, I also need to do the same for

=DAVERAGE($A:$E,5, {“EventKey”;$C2})

Which is computing my mean, so I can then calculate my zscore.

Hi there – I cannot seem to get this to work. The first row contains a formula – =ArrayFormula(sum($F2:$N2,$D2)) with required values in all columns entered from a google form. I submit a new form and the formula is not copied down

Yes that works for some of my tasks but not if I tried to CONCATENATE ranges, array formula always return to the first element, so when there is a new submission from google form, all values calculated in the following row is just the same with the top row. Any help I will appreciate to solve this. Here’s my ARRAYFORMULA won’t work with CONCATENATE

=ARRAYFORMULA ( IF(LEN(ORDER!$A$2:A), (CONCATENATE(ORDER!B2:B, (MID(TEXT(ORDER!A2,”dd-mm-yyyy”),9,2)), (MID(TEXT(ORDER!A2,”dd-mm-yyyy”),4,2)), (TEXT(A2,”0000″)) )) ,) )

it’s a formula I used to generate order ID in my spreasheet sales

Hi Ben!

Great article! I had a question:

I made a google sheet using array formula and it works perfectly. Only one problem…

I have a script which sends my data to firebase. The array formula seems to send a database entry with key=0.

Iâm assuming this is because the array formula is auto creating a new blank row?

Could you confirm that?

Thanks

Carlo

Hi,

I have a some data in sheet1 – i want it get automatically fill in blank rows with reference to change of month in Cell A1

Suppose A1 has 01-Jan-19

and A2 to C5 has some text or values

A2=1, B2=10,C2=amit

A3=2, B3=10,C3=Rahul

A4=6, B4=22,C4=Mona

A5=2, B5=11,C5=Raj

now when A1 date changes to 01-Feb-19

then in below cells from A5 to C10 the above data automatically comes down – How can we do this

I have a form populating responses onto a Google sheet on tab A. On tab B I have a formula in cell A2 that is =’tab A’!A2

When a new response populates on tab A from the form the formula changes on tab B to =’tab A’!A3. Any idea why this is happening?

Hi Ben,

Have been breaking my head over using index – match in an arrayformula. Any idea why it refuses to work?

Thanks..

Hi, I’m trying to use this function and while I understand how it works, my sheet doesn’t seem to recognize the last $B of ($B$2:$B) as a cell entry (as in the $B$2 will highlight however the 2nd part does not). Any idea why?

Hi Ben,

I’m trying to optimize speed on a series of spreadsheets with massive amounts of data that are slow due to importrange formulas, vlookups, etc. (This is one of many changes I’m making based on your “slow sheets” article.)

Using array formulas with unbound ranges (ie. A2:A) to check if there is data for all rows in the Spreadsheet seems inefficient assuming I know exactly how many rows exist.

Rather than using a formula like this, which requires a check at every cell:

=arrayformula( IF( ISBLANK(A2:A), “”, YEAR(A2:A)))

Is there a way to use a formula like =counta(A2:A) to get the number of the rows and then reference that cell in the formula to define the range?

For Example:

=arrayformula( IF( ISBLANK(CONCAT(“A2:A”, total_row_count)), “”, YEAR(CONCAT(“A2:A”, total_row_count)))

Hopefully this makes sense. Thank you.

ola boa tarde, estou precisando de um comando parecido com esse que ja tenho =ArrayFormula(IF(B2:B=””;””;ARRAYFORMULA(“” & ROW(C2:C)-1))) porem preciso saber como faĂ§o pra colocar tipo assim BR1001PO TIPO PEGAR A PRIMEIRA LETRA DO NOME E A ULTIMA ( PREDO ) BR1……PO ALGUEM PODE ME AJUDAR?

Hello,

I created a custom script to convert numbers to their word equivalent. I want to call this function for a cell in a column when a row is populated via Google form. I was trying to use ARRAYFORUMLA with something like ARRAYFORMULA(toWords(B2:B)) but the result is a cell populated with all the values merged.

What I’m trying to do is apply the formula toWords(B2), toWords(B3), toWords(B4), etc… to every cell in a column. I was hopping to use ARRAYFORMULA to achieve the auto creation of the cell for every insert when using the google form. Hope I’m making sense đ

Hello,

I have a form online that is collecting information from organizations. The form has over 41 sections and each section has multiple questions. I’ve been trying to get the information that is entered into my “source” sheet (form destination) to automatically input into each sheet (broken down by sections). I’ve attempted the array formula and the vLookup with no success. Can anyone help?

The link is https://docs.google.com/spreadsheets/d/17WlnUhlMy1mu7WyGXgnArNIjxay3qUrxywwXHUC1wyg/edit?usp=sharing

Hello!

I have a form that populates my data in a data log. Each line of data in the data log contains a time stamp. Great! What I really want though is a date only stamp. So in column C, I have a formula that does the conversion to a specific format sans time. =if(B2″”,Text(B2,”YY MM MMM DD”),””)

Example: 5/6/2019 18:18:41 should be 19 05 May 06.

Of course I have to apply my formula manually. This is a pain. I want this to be automated. I tried to apply the array formula in column C row 2 where I want my date stamp to live: =ArrayFormula(IF(ISBLANK($B$2:$B),””,Text(B2,”yy mm mmm”)))

The result is that every subsequent cell under C2 simply repeats the result of the formula in row 2. How do I get the converted date stamp value for the adjacent cell under column B into column C automatically?

Thanks!

Hi,

I created a google form that is linked and auto populating on my google sheet; this is working fine. On my form there is a question stating price impact, which is a yes or no question.

My goal is to create a formula that will auto fill with a 9000-code number for “Yes” responses. And any yes’s after that will proceed as 9001,9002,9003, and so on. This is what I have so far which only answers 9000 for any yes and N/a for otherwise.

=ArrayFormula(IF(ISBLANK($E$2:$E),”Yes”,IF($E$2:$E=”Yes”,9000,”N/A”)))

Thanks! As a result of reading this article, I was able to define an arrayresult vlookup from another Google Sheets file which inserts a full name based on an email address in a Forms response table. It looks like this:

=IFERROR(ArrayFormula(IF(ISBLANK(J3:J),””,VLOOKUP(J3:J,IMPORTRANGE(“”,”AllData!K2:AC”),19,false))),””)

Now my Forms response file automatically inserts a full name with each new response. That’s exactly what I needed it to do.

Used concatenate function in the sheet for google forms.

But when a new entry comes it doesn’t concatenate automatically.

I even dragged down the formulae through all the rows but when a new entry comes its doesn’t concatenate on its own. Need to do that manually. Please help

i want to know if it is possible to have a street seller use google form to request products to the manager of the office.

lets suppose i have 3 products and prices of each one is usd1, usd2 and usd3

seller goes out on the street for sales.

once seller makes a sell, he/she use google forms to put the request/order that will be seen by the main office to deliver the products next day.

i wanted the sum of products to show in the form as seller puts the quantity.

is it possible?

I’m trying to use an array formula, but instead of calculating each row, it is filling in the result from row 2. Here’s my formula:

=ArrayFormula(IF(ISBLANK(B2:B),””,IF(O2=”Not Approved”, O2, IF(AND(T2<now(), S2="no"),"ACTIVE",IF(T2<NOW(),"EXPIRED", DATEDIF(NOW(), T2, "D"))))))

Without the array formula added, the formula works fine. With the array formula, I can change the data and generate any of the possible results, but that result will always appear in subsequent rows. What am I doing wrong?

After sleeping on it, the answer came to me – should have been obvious yesterday. I needed to have the range of cells in the calculations not just in the ArrayFormula section. So now it reads:

=ArrayFormula(IF(ISBLANK(B2:B),ââ,IF(O2:O=âNot Approvedâ, O2:O, IF(AND(T2:T<now(), S2:S="no"),"ACTIVE",IF(T2:T<NOW(),"EXPIRED", DATEDIF(NOW(), T2:T, "D")))))).

And it works.

Hi Ben!

Is it possible to make an arrayformula to :

=if($I2=””,””,I2)*(index(Prislista!$A$5:$E$10,match($H2,Prislista!$C$5:$C$10,0),match(S$1,Prislista!$A$1:$E$1,0)))

When I try:

=ArrayFormula(if($I3=””,””,I3)*(index(Prislista!$A$5:$E$10,match($H3,Prislista!$C$5:$C$10,0),match(S$1,Prislista!$A$1:$E$1,0))))

Nothing happens? Is it possible to use array to this formula or do I have to copy the formula down?

Hi, I have a sheet collecting the responses to a form. I need to analyse the data in a separate sheet so I have the formula =sheet1!A1 to get the data across. When a new person submits, a new row gets added to my analysis sheet so I would have to recopy the formula down each time… how do I use an Array formula to stop this? TIA

Hey there. Thanks so much for this article. Can someone help me with this form results sheet I’m trying to work with ? What I’m trying to achieve is that the Date field is taken and some days are subtracted with an Array formula. Because the calculation is quite complex (lots of conditional elements), I’m unable to make the array formula work.

The spreadsheet is visible here (Array test sheet): https://docs.google.com/spreadsheets/d/1dFXYemxTQDKcl79JbqYNgvTdK7qCiKuB0HdgTUpFCCE/edit#gid=396444811

The calculation formula the following:

=IF(AND(O2=”ZĂŒrich”,E2=”One-Shot”), J2-7, IF(AND(O2=”ZĂŒrich”,E2=”One-Shot Series”), J2-7, IF(AND(O2=”ZĂŒrich”,E2=”Mini campaign”), J2-14, IF(AND(O2=”ZĂŒrich”,E2=”Campaign”), J2-14, IF(AND(O2″ZĂŒrich”,E2=”One-Shot”), J2-14, IF(AND(O2″ZĂŒrich”,E2=”One-Shot Series”), J2-14, IF(AND(O2″ZĂŒrich”,E2=”Mini campaign”), J2-21, IF(AND(O2″ZĂŒrich”,E2=”Campaign”), J2-21, J2))))))))

The formula works fine (you can see it in the Control column). I am able to add the array formula but as soon as I put cell ranges (for O columns and/or E columns), the calculation stops working and gives me the same value as the Date column.

Thanks for your help.

I get a value from form response as => “1(current), 3(current)..etc.”

I am able to split it as SPLIT(A2, “(current)”). But it does not get affected for new responses.

How shall this be handled?

Looking to use something like this in my Google Form, but I do not want it to have a cumulative effect in the Array Function, and I can’t figure out how to change your formula to meet my needs.

I am looking for one column (say, solumn C) to automatically be populated with the sum of a few other form items from different columns (columns E & H) in my case. In each row, Column C would have a different number, based on entries from Columns E & H. Perhaps it is the use of the $ in the code, which I don’t quite have figured out.

Can’t quite seem to find a way to get this done…any help would be appreciated.

Thanks!

I have a massive mess of If statements I need cleaned up. If anyone can tell me how to make an array work in Google Sheets with this unholy mass of code so that I can make it work for over 1000 rows. There are about 120 lines that I need to have matched from Column B to Column A. If you have a solution for this, I would love to hear it. Below is a sample of my massive code, as the real thing is confidential.

=IF(E2=’Sheet 1′!B2,’Sheet 1′!A2,IF(E2=’Sheet 1′!B3,’Sheet 1′!A3,IF(E2=’Sheet 1′!B4,’Sheet 1′!A4,”N/A”)))

Hi Ben I am using the same formula in my sheet but its not working.

Its just adding a new row and removing the old formula from the sheet.

I m using a below formula to get the values in one column in response sheet.

=ARRAYFORMULA(IF(LEN(A2)>0,index(D2:J2,MATCH(FALSE,ISBLANK(D2:J2),0)),””))

Thanks

Hi Sir Ben,

just want to ask when im done filling up on google form and automatically be transferred to google sheet, all cells on the row is being replace by my answer in google form. i have there a formula on the last cell that it shouldnt be replace anything but it also been replaced. but yours, cell c so on doesnt been replaced. how to fix it.

thanks!

My sheet collects data from a Goggle Form. A specific value that is provided by the Form will determine a “rate” on another column through an arrayformula. That rate is determined through another arrayformula which mixes CHOOSE, INDEX and VLOOKUP.

Such specific value on the first row is affecting the rates on subsequent rows. Does anyone have an idea what might ne wrong??

Hi Ben, is it possible to import every Nth Cell from another Sheet. I am trying to import every 6th cell from Column C. I’ve tried doing this but I keep ending up with the imported data appearing every 6 rows. I then tried to sort the range this compiled the data but also alphabetised it which I don’t want. Thanks!

Ben! I love this site! Thank you!

I have a question for you (or any of the other people who frequent this site. Is it possible to edit values that are returned in an ARRAYFORMULA column and have them overwrite the original data?

For example, I have a Google form that is feeding responses into Appsheet. In order to create all the tables, I’ve created three other sheets and added ARRAYFORMULA to multiple columns on these sheets in order to get the data organized into the tables I want. If I open Appsheet, and edit one of the values that represents a cell in an ARRAYFORMULA column, the cell itself is edited, however, the orginal data from the Google Sheet is not. I’m looking to have my edit overwrite the original Google Sheet response.

Please let me know if you would like to see the Google sheet in reference.

Hi Ben I am loving your column.

I wanted to find a formula that would automatically populate a date into a cell every time another cell was not empty and only when a new row was added to the sheet from a form that was filled in using my app.

After searching for hours on the internet I have finally got an array formula working on my google sheet thanks to your posts.

It updates a cell with the current date and time (once I formatted the column for date) based on an entry into cell R or every time a new row is added. I am using a form filled in using my app. It is working perfectly and the code is as follows.

=ArrayFormula(IF(ISBLANK($R$2:$R),””,NOW()))

My issue now is that once the cell is NOT empty, the date still automatically updates with the current date and time, every time the sheet is refreshed or newly opened.

Is there any way to restrict it to only update when the value in cell R is either blank or has changed but not from refreshing the page?

Cell R is a checkbox and I want the date to reflect the last change to that cell.

Hope you can help. Thanks

Sory that was supposed to read

Is there any way to restrict it to only update when the value in cell R has changed but not from refreshing the page?

Hmm, I wanted to translate the text from column A into column B but this array formula simply translates the words from A4 into all of the cells in column B. Any idea what I’m doing wrong?

=ArrayFormula(if(isblank(A4:A1000),””,googletranslate(A4:A1000,Key!$C$2,Key!$D$2)))

Hi Ben, thanks for your practice example.

I have a problem arrayformula fuction with min or max.

How can you find a min or max value in every single row, using the arrayformula function?

I have searched the various posts on the web, but there are ineffective solutions.

Sorry my english … but I’m an Italian :o)

Nice to meet you…Andrea

Hi Ben thanks for this!

You wrote on a side 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 can I create a new sheet that automatically populates rows when that form is submitted without having to manually copy each row?

Thanks!

Hi Ben,

Can I get this to work with an array?

=AVERAGE.WEIGHTED(O6:O,C6:C)

However, column C has more rows populated than O. I need the formula only to work on rows that have data in O. Data is coming in daily from a form.

hi

i have a simple need. i have a google form that saves the results in a google sheet. i have a lot of columns, some of them with values (prices in mi case).

i just want to make a formula to make a summatory of this values…but i need that this formula its added automatically with every new form response

a simple formula in the cell should be =i2+43+j2+28…etc etc

obiously this formula its dropped down or deleted with every new entry in the sheet

arrayformula seems what i need but i cant get it work. too much sintax for me, i think i dont need IF function… etc etc

how should be the formula to simply sumatory or multiplications…and i should pout it on the row 2?

https://docs.google.com/spreadsheets/d/1omhFWD4stAd6HollJdebvevV_g87WbJCOOuxEA_u9yY/edit?usp=sharing

thanks a lot