Questions tagged [google-sheets-arrayformula]
Use for questions about the use of the Google Sheets built-in function ARRAYFORMULA.
697
questions
0
votes
0answers
7 views
#REF error when changing Drive location
I have a Google Sheets template that locations are making a copy of. A small percentage make the copy successfully and are able to use then randomly the formula's return #REF. All formulas are ...
-2
votes
0answers
21 views
Have to get access to my database
Here is the I got: Error linking spreadsheets. Make sure you have access to the sheet you're trying to import from.
Have can I have access to my DAtaBase spreadsheet. I am importing my contacts to my ...
0
votes
1answer
22 views
Trying to get array formula to work on Sheets
I am trying to to use the Array formula that includes a minus and divide value. Here is my formula:-
IF(J3:J="",,SUM(((B3:B+C3:C)+(E3:E+F3:F))-$X$3)/$X$3)
I tried inserting MINUS( before the ...
-2
votes
1answer
30 views
i can't find the problem to this formula
=UNIQUE(QUERY({IMPORTRANGE("1xWx1FRfvCfmvYi9Sb7Gh5lIAb-NAt0RG0h-e_HKmxjU","APP Directory!A2:U");IMPORTRANGE("1xWx1FRfvCfmvYi9Sb7Gh5lIAb-NAt0RG0h-e_HKmxjU","APP ...
0
votes
0answers
14 views
Advanced Array Formula Documentation / Multidimensional Arrays / Conditional Variable Ranges
I'm manually tracking games and wins every few days. Games goes up by 3-20 with each update, wins is about 50% of games. So data looks something like this:
Games Wins New Games New Wins
2800 ...
0
votes
1answer
14 views
Google Sheets Combining Is Between and AND operator Conditional Formatting
my programming knowledge is not great and I need some help. I am trying to get conditional formatting to highlight any cell in a column that contains values between 13-16 AND in a different column (...
0
votes
2answers
24 views
Is there a way to remove entries based on condition in =filter?
I probably did not phrase my question accurately. Is there a way to remove the result 'a' because it fulfils the '<>1' condition? I am pretty sure a mixture of query and arrayformula is needed ...
0
votes
1answer
27 views
Reference "This Cell's Column" in Google Sheets
https://docs.google.com/spreadsheets/d/1LG7AgcumhfQ1ZV0Em_oVjdPdrLCP-5CWWrw0WV1oCnY/edit?usp=sharing
(We have to track 3 separate point totals for everyone who has participated in each game, game ...
0
votes
2answers
33 views
How to show a report that has "numbers in thousands" as full numbers?
If I have a lot of numbers in thousands and I don't want to use regexp to append to them three zeros to see the real number how can I proceed? I didn't found yet a formatting solution or any other ...
1
vote
0answers
8 views
Retrieve URLs from website tables [duplicate]
I'm trying to save table data from a website by using IMPORTXML and IMPORTHTML. These tables include text/numeric data, which I can retrieve with no problems, and hyperlinks wich I cannot retrieve. ...
0
votes
1answer
25 views
Google Sheets - Arrayformula with textjoin, and, if
I'm trying to figure out how to make my arrayformula with textjoin to work, but the formula does not copy to another row.
I want the job no automatically generate when i write data in F3 cell & ...
0
votes
1answer
12 views
Google Sheets IFS Text result based on multiple conditions FIX & SORT
I'm very new to Google Sheets and I'm trying to make find the correct formula for a text result based on multiple conditions, here is my best crack at it:
=arrayformula(IFS(B9:B="",V9:V=&...
0
votes
1answer
9 views
How to assign a name to a membership number vice versa?
I would like to learn what formula I can use to make my timesheet more efficient.
How to assign a membership number to a name vice versa? I need to add lesson hours to each membership daily and ...
1
vote
1answer
24 views
Google Sheets, Referring to a cell range
Here's what I am trying to do.
In cell C15, I put the cell reference of another cell, e.g. B2.
I another cell, cell C16, I want to check that IF the value in cell C15 contains the cell reference to ...
0
votes
0answers
30 views
How to create a google sheets formula to return a value based on % values in other cells & weighted average formula? (FIXED)
I've been trying to create a scoring system for expected stock market returns -
Sheet
I'm having problems creating a formula to firstly give stocks a score in multiple conditions for example SS
I want ...
0
votes
1answer
27 views
Arrayformula to get the max number of consecutive occurrence of a character inside a string
I have strings of different length like the one below:
LWLLLLXWLWWWXWWWXWWLWLWLWWWWLWLLLXXWLLLLL
From this string, I'd like to extract the maximum number of consecutive occurrence of L. Here it would ...
-1
votes
1answer
35 views
Get a single row with true and false if the string is contained in a range
First of, sorry for the title, I have no clue what to search for or how to name the question.
I got an example sheet which might already explain everything:
https://docs.google.com/spreadsheets/d/...
0
votes
1answer
22 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,...
0
votes
2answers
21 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 ...
0
votes
1answer
21 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 ...
0
votes
1answer
26 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 ...
1
vote
1answer
36 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 ...
1
vote
1answer
40 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 ...
0
votes
1answer
49 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" ...
0
votes
0answers
52 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 (...
0
votes
0answers
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 ...
1
vote
0answers
39 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 ...
0
votes
1answer
65 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)
...
0
votes
1answer
28 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 ...
0
votes
0answers
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 ...
1
vote
1answer
25 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 ...
0
votes
1answer
25 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 ...
1
vote
2answers
36 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 ...
-1
votes
1answer
68 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 ...
0
votes
0answers
9 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 ...
0
votes
1answer
48 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 ...
0
votes
1answer
65 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 ...
0
votes
0answers
22 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 ...
0
votes
0answers
18 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
...
0
votes
1answer
23 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
1answer
34 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 ...
0
votes
1answer
22 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 ...
0
votes
1answer
44 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 ...
0
votes
1answer
20 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 ...
0
votes
2answers
61 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&" ...
0
votes
1answer
70 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))...
0
votes
0answers
9 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.
...
0
votes
1answer
27 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. ...
0
votes
0answers
76 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 ...
0
votes
1answer
33 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 ...