Web Applications

Questions tagged [google-sheets-arrayformula]

Ask Question

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

700 questions
Newest
Active
Bountied
Unanswered
Filter by
Sorted by
Tagged with
0
votes
0answers
3 views

Flexible formula to match strings in 1 row to run a formula in matching columns in another row

I have a row where all strings have the identical format (XX-#####-##) in which Xs are 2 upper case alphabet letters and the #s are integers. Short example, see the doc for more KC-30000-00 | KC-30000-...
0
votes
1answer
53 views

Compare and remove duplicates in Arrays (Google sheets)

I have two arrays of many rows , in my Google sheets, which have numbers such as Active Recovered 904;1505;1706 406;504;1502;1503 202;401;602;1104;1501 202;1502;1602;1704 I have shown only two ...
0
votes
1answer
19 views

Calculate specific contents of a cell that contains a string of numbers and text

I have a sheet that contains cells that have lists of data within them, for example: One cell contains: 1xExample, 2xAnotherExample, 5xThirdExample The cell below contains: 1xAnotherExample, ...
0
votes
0answers
8 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
22 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
23 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
15 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
16 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
25 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
28 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
36 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
28 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
21 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
32 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
28 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
23 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
22 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
41 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
53 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
40 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
70 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
29 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
11 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
67 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
23 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
23 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
45 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
67 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
74 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. ...

1
2 3 4 5
…
14

Your privacy

By clicking “Accept all cookies”, you agree Stack Exchange can store cookies on your device and disclose information in accordance with our Cookie Policy.