2022 Developer Survey is open! Take survey.

Questions tagged [google-sheets-arrayformula]

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

Filter by
Sorted by
Tagged with
0 votes
1 answer
38 views

Splitting a list contained within a single cell and want to associate/add text from a different cell in the same row to the new separated list

I am using Google Forms to collect information. In google sheet, I have rows of data and one of the cells contains lists that needs to be separated out. =query(FLATTEN(ArrayFormula(IFERROR(SPLIT(G2:G,...
user avatar
  • 3
0 votes
2 answers
23 views

Google Sheets: Make the cell data the name of the link

Update: I have attached a pic for example. -------- I have ID numbers in each cell and I want that number to be a link to their file. I know how to make a separate hyperlink that will ref that cell to ...
user avatar
  • 1
0 votes
1 answer
24 views

Google Sheet how to combine data from same sheet to other sheet

I have several tables on the same sheet with different numbers of rows and columns but basically have the same structure, now I want data from these different tables to be united in one master ...
user avatar
  • 3
0 votes
1 answer
29 views

Need To Randomize Selections From A List But Ensure Uniqueness

I'm building a tool to randomize setup for a very large modular card game. There are several lists from which I need to select 2 to 6 non-duplicate entries, and each list is on a separate tab from the ...
user avatar
1 vote
1 answer
45 views

Arrayformula to get closest non-numeric value above

I have Column A with mixed data types (numbers and strings) and in Column B I want for each numbers in Column A to display the string that is the closest above. Example: Title| 1|Title 2|Title Next ...
user avatar
1 vote
1 answer
77 views

How to copy a cell from Google Sheets without getting double quotes (" becoming ""), BUT keeping the \n (char(10)) line breaks

I have a couple of Google Spreadsheets I use to generate config files for things. The config file data ends up in a single cell, which is copied and pasted. Problem is Google Sheets has this odd ...
user avatar
  • 131
0 votes
1 answer
83 views

Google sheets reference Text in Cell in Array Formula & Countif formula

So, I have a Array Formula and Countif formula in place because I need to know the total of entries in a combination of ranges. I need to know the sum of the number of cells that have "X" ...
user avatar
0 votes
0 answers
53 views

Array Formula Problem - How to Fill In Blank Rows with the Same Value before a Breakpoint which advances to the next letter

I have a Google Sheet which is grouped into segments of a TV show. So there's an A segment and then a B segment and so on ... I have allocated two columns to provide two different timing functions (...
user avatar
  • 3
0 votes
0 answers
10 views

Google Sheets - Repeat row in coordination with other row [duplicate]

So I've been looking to do this for a long time hours now, maybe I haven't used the right search terms, but I can't find any working formulas on any discussions! Here's what I want to do: So here's my ...
user avatar
  • 101
1 vote
0 answers
52 views

Use ArrayFormula to automatically update the column with a formula already containing an ArrayFormula

I am using the below formula to concatenate some data into one string: =TEXTJOIN("",FALSE,ARRAYFORMULA(IF((Data!$F$3:$F=$C2)*(Data!$G$3:$G=$D2),Data!$L$3:$L,""))) I however have ...
user avatar
0 votes
1 answer
213 views

Query Divide by Sum Column getting Error in Google Sheets

Looking to work around with error message "Add_col_to_group_by_or_agg:Col1" from formula below: Query(Query({data range},"Select etc."), "Select (Col1*Col3)/Sum(Col1)",0) ...
user avatar
0 votes
1 answer
45 views

Google Sheets Query - Divide by Sum(Col), Error

I'm doing a simple query but keep getting the error "Add_col_to_group_by_or_agg:Col1". The formula is =QUERY({L42:N63}, "Select (Col1*Col3)/Sum(Col1)",0) What I'm looking for is ...
user avatar
0 votes
0 answers
28 views

Referencing three or more ranges and combining the dates that result

I have this formula that I made that takes a range from a cell (in this case, 10/5 - 10/11), subtracts the dates from that range from a larger list of all remaining days in the year, giving me a list ...
user avatar
1 vote
1 answer
26 views

Sorting consecutive dates into groups

I have a list of dates. The list has chronological gaps in it, but I'm looking to bring it all into a single cell of date ranges separated by commas, organized by consecutive dates. Example of the ...
user avatar
0 votes
1 answer
34 views

How to make Row() formula dynamic based on a cell content?

I am creating a multiple choice (see column A) and I want it to be randomized (see column C). I saw a formula in the internet, modified it, and it really does randomized the contents of each cell in ...
user avatar
  • 3
1 vote
2 answers
46 views

Array Formula omits IF / AND functions

I'm using a series of IF and IF/AND functions to assign a set value according to, sometimes one set or two set of colums. While creating a range and Array Formula to apply that to all posible new ...
user avatar
  • 11
-1 votes
1 answer
71 views

Inventory Management where main list highlights items checked out/ in

I am working on an inventory management list where barcodes are scanned out and scanned back in. My sheet is attached to forms. I have set the item sku# on the main inventory list to highlight red ...
user avatar
0 votes
0 answers
32 views

Google Sheets-Why is my countif, weeknum arrayformula miscalculating for the 52nd week of the year but works fine in calculating other weeks?

In Google Sheets, I have an array formula with countif that counts attendance based on week number. But for some reason on the 52nd week of the year, it counts all the blank cells. The answer in the ...
user avatar
0 votes
1 answer
70 views

Google Sheets - Return a text value based on another cells text value

I have a set group of "program names" in a column, and want to have another column automatically write the matching "acrononym" for its sister value. For example: Master in Finance ...
user avatar
0 votes
1 answer
104 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 ...
user avatar
0 votes
0 answers
26 views

Google Sheets: SUM of the LAST value - apply across multiple columns

Use Case: I have multiple financial portfolios that cannot be connected to via API. I want to track my total balance across portfolios. I do not want to tally every portfolio every day - instead, I ...
user avatar
  • 101
0 votes
0 answers
30 views

Google Sheets - assign a year and date rank in ARRAYFORMULA

I want to number production runs in my system based on a continual count for that year, which is then preceeded by the year (with duplicates sharing the same number). For example, if the data set was ...
user avatar
0 votes
1 answer
40 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 ...
user avatar
1 vote
1 answer
40 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 ...
user avatar
0 votes
1 answer
28 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 ...
user avatar
  • 145
0 votes
1 answer
61 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 ...
user avatar
  • 17
0 votes
1 answer
30 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 ...
user avatar
  • 145
0 votes
2 answers
143 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&" ...
user avatar
0 votes
1 answer
145 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))...
user avatar
0 votes
0 answers
10 views

Check if Column contains value, when found return sum of two cell

I'm using a Google Drive spreadsheet to my warehouse. I need to find a value (description) of a cell from a column and if I find it sum the quantity of this description + quantity of my warehouse. ...
user avatar
0 votes
1 answer
46 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. ...
user avatar
  • 3
0 votes
0 answers
150 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 ...
user avatar
0 votes
1 answer
38 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 ...
user avatar
  • 3
0 votes
1 answer
25 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 ...
user avatar
  • 1
1 vote
1 answer
57 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 ...
user avatar
0 votes
1 answer
63 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 ...
user avatar
0 votes
1 answer
88 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 =...
user avatar
2 votes
0 answers
28 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....
user avatar
  • 195
0 votes
1 answer
51 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(...
user avatar
  • 195
0 votes
1 answer
43 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 ...
user avatar
  • 195
0 votes
1 answer
405 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 ...
user avatar
  • 195
0 votes
1 answer
47 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, ...
user avatar
  • 195
0 votes
1 answer
36 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 ...
user avatar
  • 195
2 votes
1 answer
106 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.
user avatar
  • 195
1 vote
1 answer
330 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 ...
user avatar
  • 195
-1 votes
1 answer
145 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 ...
user avatar
  • 195
0 votes
0 answers
59 views

Array Forumlas and Sumifs in Google Sheets

I have a inventory spreadsheet that I am using to keep track of my inventory and I want to add a new location to track. I added the location into my forms and they populated on the spreadsheet fine. ...
user avatar
0 votes
0 answers
19 views

Sort function in Google sheets shows empty output when sorting on a column with dates

I have a form response sheet with an ArrayFormula to either show the date from a form field or, when this is left empty, the timestamp date. This column is the master datetime column I want to sort by....
user avatar
  • 1
0 votes
2 answers
39 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) ...
user avatar
  • 223
0 votes
2 answers
2k 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 ...
user avatar
  • 195

1
2
3 4 5
15