Use for questions about the use of the Google Sheets built-in function ARRAYFORMULA.

802 questions
Filter by
Sorted by
Tagged with
152 views

Create A Result Limit for Array Column. Spill Into Next Column. (Google Sheets)

I'm looking for a formula that lets me take a dynamic column range and tell it to spill into the next column if it exceeds a certain number of rows. The most rows I want in one column is 10,000. The ...
61 views

Stacking A Range of Columns Into One (W/O 50,000 Limit)

The Problem: I have somewhere between 20 and 30 columns of varying lengths (dynamic and can change). I would love the ability to be able to stack them in Google Sheets. I have found some solutions ...
1 vote
48 views

Google Sheets - How to use results from INDEX formula in a new QUERY function

In Google Sheets I have an =INDEX function which is producing a number of references for data in another worksheet. I need to use those references in a QUERY function. I've not found a good way and ...
67 views

Google sheets conditional format to highlight a column containing items from another list

I have rows of data, Col. A will be the order number and there will be duplicates of the order number (this is for each individual item purchase and MUST NOT be removed to give unique numbers). Then I ...
• 191
85 views

Running total in Google Sheets leaving blank cells if criteria not met

I would like to keep a running total in column A if certain text is present in column B. If that text isn't present, I would like column A to be blank, but pick up counting where it left off on the ...
• 27
57 views

Copy/duplicate entire rows based on number in another column

I want to be able to copy/duplicate several columns of data based on their qty in another column. I have my data in col. A- C & the qty in D. Basically it is "A-C" times D. And on the ...
• 191
397 views

How to solve error The resulting array was too large in MMUlLT formula with ~9700 rows

I need to calculate concatenation of the value and how many times it was used in rows as below: I tried to use this formula by closed-end references =ARRAYFORMULA(IF(LEN(L3:L9700),M3:M9700&" ...
312 views

How to solve Error message in Google Sheet array formula: The resulting array was too large

I tried to use this formula below on my Google Sheet for around 10,000 row data: =ARRAYFORMULA(IF(LEN(L3:L),M3:M&" - "&MMULT((M3:M=TRANSPOSE(M3:M))*(ROW(M3:M)>=TRANSPOSE(ROW(M3:M))...
120 views

Sum the current row plus the next 6 (7 total)

I have a large spreadsheet and I want to sum the current row + the next 6 rows in a computed column (in this case, column O). So if I'm on row 21, for example, I want to sum column G from G21 to G27. ...
375 views

How can I use ARRAYFORMULA with RANK?

I want to use ARRAYFORMULA with RANK in google spreadsheet but I have no idea about it. Here's example A B C D 1 X Y Z Rank of X 2 4 6 1 2 3 1 3 2 3 4 3 2 1 1 In this case, I can write ...
47 views

How to randomize words contained in a cell separated by Alt+Enter [ char(10 ]

How to randomize words contained in a cell separated by Alt+Enter [ char(10 ] , like a new line. I found a formula but what it does is create a range of columns first. But I can't do it because there ...
45 views

Create a list of names of those who answered a question correctly

I have a finished Google Form connected to a Google Spreadsheet - I want to list the names of everyone who answered a specific question correctly. I've spent a while trying to figure out how to do ...
• 1
1 vote
84 views

Apply Concatenate formula to entire column

How do I apply the formula as described here.. https://webapps.stackexchange.com/a/76395/279173 which is this part: =CONCATENATE(""&LEFT(A1,10)&" ...") to an entire ...
111 views

Array formula works in the sheet but invalid in conditional formatting

I have a list of time intervals along with their convenience ratings. I wish to highlight intervals rated anything other than impossible which conflict with those rated impossible. I use start1 and ...
1 vote
208 views

referencing a column in a Google sheets array literal

Is there a way to refer to columns in a literal array like so: =FILTER({lengthy_arrayformula_1, lengthy_arrayformula_2}, condition referencing column 2 of the array literal in previous argument) or =...
70 views

If there anyway i can check if the cell contains the result of a arrayformula()?

For example i have this in A1 : =arrayformula(B1:B100) If in A10 i accidentally enter a value (which will overlap with arrayformula result) , it will produce error in the A1 because of existing value....
• 235
171 views

Grab the right most value using arrayformula

I have this sheet : https://docs.google.com/spreadsheets/d/163jhtdKcC6Pu34jX9brolZDb5bm-PWqG7i-yAtNueoo/edit?usp=sharing Trying to get the right most value per row Basic formula : =FILTER(B2:2,COLUMN(...
• 235
201 views

find counta() per row using arrayformula()

I have this table : https://docs.google.com/spreadsheets/d/1ICEL33X3ADz7XEH2Cmi1p6mrYT2RqPwbtBjCHWoNkoA/edit?usp=sharing Basically i want to do counta() per row but using arrayformula(). The basic ...
• 235
668 views

vlookup with multiple output and combining filter() with arrayformula()

I have 2 sheets : shot and deadline. https://docs.google.com/spreadsheets/d/1drNUQVtAxd0HU4QCVSKJSsKHoanE735n8HsjkYpYiGs/edit?usp=sharing This is my shot sheet : and this is the deadline sheet : So ...
• 235
93 views

Can we do arrayformula for repeating horizontal join?

I have this table : where i want to join all data in each row start from column D to last column. I want the output just like in that C2:C5 I use this formula in C2 : =JOIN(",",filter(D2:2, ...
• 235
78 views

Why my Arrayformula doesn't work

I have this google sheet : link is here : https://docs.google.com/spreadsheets/d/1_-AcgJud2DclKtarcJj39DNB84CzthUzO_n5F8mBgKY/edit?usp=sharing On the top is my matrix table. It has key on the left ...
• 235
109 views

How can I repeat a value into a range using a formula?

I want to do something like this: The value can be a character or some words. If the value is number (for example = 0) then I can do: sequence(11,6,0,0) But I can't use a character or words.
• 235
1 vote
705 views

Google-Sheets : ARRAYFORMULA() for multiple columns

I have this table : https://docs.google.com/spreadsheets/d/100ij18btv5VTmnwwdaFJ8U3PEzkS8Z-duEn2PKgA6sc/edit?usp=sharing There are 3 Sheets inside it. The idea is to get how many ingredients used each ...
• 235
1 vote
318 views

How to combine formulatext() and arrayformula()?

I want to use formulatext() with arrayformula() : =arrayformula(formulatext(A1:A10)) But The arrayformula doesn't work. It should output 10 rows but it only returns a single row output. Is there ...
• 235
43 views

Print True or False in calculated column using ARRAYFORMULA

I need to output either TRUE or FALSE in column E, based on the following criteria: If A2 = "stackexchange" then (always, regardless of other data) print TRUE in E2 (otherwise print FALSE) ...
• 223
3k views

How to use max() with arrayformula()

This is my sheet : in column E, i want to find the max among col A,B,C using this simple formula in E1 : =max(A1:C1) But rather than pasting the formula to E2 .. E10, i want to see if possible to ...
• 235
40 views

Google Sheets, use arrayformula to get average score for each classroom from a list of all students in all classrooms?

Whilst not actually referring to students and test scores, the following hypothetical is the easiest was for me to explain the question. Sheet A contains a list of all classrooms, sheet B contains a ...
• 23
283 views

Apply custom formula across all columns using Array Formula

I have trouble applying my custom formula across all columns using Array Formula. My custom formula is named getCountry where it could get the country based on an address. The address components e.g. ...
• 21
447 views

Using Cell Value as a Worksheet Name in Formula

I wanted to use a cell's content as a sheet name, i.e. A1 A2 A3 A4 South North East West and use the cell values (South, North, East, West) to reference cells from sheets called South, North, East, ...
51 views

Combine data from multiple sheets to create a stacked line chart

I have multiple sheets with three columns each: Date, Invested Amt, and Return Amt. I am trying to merge the values and get a Result dataset to create a single stacked line chart of Invested vs Return ...
16 views

Google Sheets pulling more data than the criteria specified

I have a tab in my Google Sheet for my raw data. I have another tab which has the report form which includes to criteria fields which I want to be able use to pull the appropriate data from the raw ...
19 views

Trying to combine two (2) statements together

I am trying to pull data based off of selections made in a dropdown list, and would like to combine two (2) statements/formulas into one. Separately, they both work well and achieve what I want them ...
252 views

Can't format hardkey date data as a date in Google sheets?

I've got some date data in a Google drive sheet which is imported from a CSV. The date data is formatted as either dd/mm/yy or dd.mm.yy as hard key data (eg. not formatted as a date) I got to format &...
• 6,565
1 vote
123 views

Google Sheets, using VLOOKUP and ARRAYFORMULA to search multi-line cells

I'm trying to use VLOOKUP to find the batch number of a product based on its serial number. The problem is that the serial numbers for each batch are stored in one cell per batch, separated by ...
• 23
141 views

How to make variables dynamic in ARRAYFORMULA/QUERY Combination

I'm trying to self populate the first column of the "Main" sheet with a query that returns the first column of the "Finance Office" sheet that matches all three names. I came up ...
39 views

What is the Google Sheet query for getting rows excluding non matching rows

The tables will have multiple entries for amount debited and credited details. It also has 5% debit and credit and 1% debit and credit entries as well. Data includes unique flag for districts, Date ...
23 views

How to apply formula to new rows that are generated by script [duplicate]

I have a spreadsheet where new data into rows is added every hour. I tried using arrayformula but couldn't figure out how. Example spreadsheet: https://docs.google.com/spreadsheets/d/...
• 99
31 views

Google Sheet: Array formula

I made this formula: ={filter(Sheet1!B1:AA22;Sheet1!B23:AA23=SAND);filter(Sheet1!B26:AA47;Sheet1!B48:AA48=SAND)} (The "=sand" is just "=true" in my language) But I keep getting &...
• 3
66 views

Google Sheets SUMIFS - apply REGEXTRACT to sum_range?

I have the following Google Sheets table and want to calculate the total sums of various medications prescribed: A B C 1 Type Amount (in g) Daily dosage (in g) 2 A 60 2 3 B 25 mg/capsule 30 pieces,...
26 views

Return different value for condition in ARRAYFORMULA

I have an ARRAYFORMULA that combines text with a number. A B 1 textA 0 2 textB 1 3 textC 2 =ARRAYFORMULA(A1:A3&" "&B1:B3) But if the value in the B column is 0, I want the ...
17 views

Moving average up until every listed week

I'm trying to automatically calculate a new average every week, so I can see the evolution of the average over the past weeks. I have one column with the week numbers. The second column contains an ...
15 views

Array Formula Issue

So I have ArrayFormula checking Column C Price to see what range it falls in. Then it assigns the number based on that range it puts it in Column M. It works perfectly as a formula but when I make it ...
1k views

How to have a query wrapped in an arrayformula auto-populate many rows

I have a query that queries over a set of rows. I want to run this query, itself, for each row in another set of rows. The one value that changes in the query for each row is something in the where ...
• 249
100 views

Can I implement this moving average with missing periods using ARRAYFORMULA?

I have these columns: A person_id B date C cupcakes_eaten D date-35 (35 days prior) There can be multiple rows with the same person_id and same date. There can be days with zero rows for a ...
214 views

cannot drag query across multiple rows (overwrites data)

Is it possible to click and drag queries? I describe my problem below and also have a reference sheet. cells A1:B5 Category Withdrawal CatA 4 CatB 2 CatA 6 CatB 11 cells C1:C3 Check For CatA ...
• 249
322 views

Arrayformula with large function for multiple columns in Google Sheets

I was wondering if anyone had a method for finding the second or third largest numbers in a row across multiple columns using the large function in Google Sheets using an arrayformula to account for a ...
211 views

How to convert text to a number in Google sheets

I've built a form that dumps data to a Google spreadsheet and that data, studio reads it... and turns that into a dashboard.. only problem is the text doesn't convert very well for building a dash ...
38 views

How to create a IF formula that searches for multiple (more than three) text options

I'm trying to create a Google Sheet to track pitches (I'm a baseball coach). I have a current sheet that I have a check box to click if the pitch was a strike or not, and then you have to choose a &...
• 27
1 vote
208 views

Google Sheets: VLOOKUP formula for a multi line cells range

I have here a sample project, where columns A to C are in FORM RESPONSES TAB/SHEET and columns D and E are in RECORDS TAB/SHEET. In the RECORDS I have a list of names, in alphabetical order, and a ...
27 views

attempting to compile an output List, into a single cell in one Sheet, based on matching criteria in a range on another Sheet

I found a proposed solution on Stack, but I can't get it to work. https://webapps.stackexchange.com/q/120111/270308 Starting formula: =IF(G3<>""; JOIN(CHAR(10); FILTER(ARRAYFORMULA(...