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
1answer
1k views

Increment number in array formula based on a condition

I have a data set with Sub-Total rows at various intervals. As the data can have rows inserted or deleted, I am trying to use an array formula to give each group of data an incremented integer as a ...
6
votes
6answers
29k views

LEFT JOIN two and more tables in Google Sheets

Say I have two tables. Table A (material requirements): product_id | material | required 1 | A | 0.5 1 | B | 0.7 2 | A | 0.2 3 | A | 0....
0
votes
1answer
16 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
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
15 views

Look for a value that might be in many columns

Say I have a value in $A, to match to another sheet of the same file. That value (in this case, a student id) might appear in many columns of the other sheet. In the following example, I am looking ...
0
votes
1answer
53 views

Create sequential dates with each date repeated multiple times

For every day of the year, I need the day repeated in 10 rows. How can I accomplish this efficiently? e.g. 01/01/2019 01/01/2019 01/01/2019 01/01/2019 01/01/2019 01/01/2019 01/01/2019 01/01/2019 01/...
0
votes
1answer
59 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
2answers
286 views

Replace value in field automatically (auto-correct?)

I'd like to replace occurrences found in Column A (cells A2:A200) so that if I enter 2/02/002 or 343 or 98/098 it auto-replaces it with UKD002/UKD343/UKD098. Is this possible?
0
votes
1answer
40 views

How to auto-calculate without a need to drag down the formula?

How can I fix formula to a row so that when data is inputted in any cell within that column, it auto-computed in Google Sheets? the % doesn't work Without having to drag the formula down. Example: ...
0
votes
2answers
1k views

How to remove duplicate rows based on a column (leaving first one out) in Google Spreadsheets

I am trying to remove duplicates from my Google spreadsheet file. Here is an initial table example A B C D HelloWorld1 2 6 41 HelloWorld1 4 3 5 HelloWorld2 6 46 6 ...
0
votes
1answer
50 views

Add fields and update array and scripts

I want to add some questions to my Google form but my response sheet has a script and an array, which do not like that. Here is what it should look like: The URL is the link for the form. Here is ...
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
1answer
1k views

Auto-populate cells based on previous cell values in Google Sheets

I have two columns with information. Values in Column B have been manually added to match the values in Column A. Is it possible to have a function that auto-populates column B with the right values ...
1
vote
1answer
104 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 ...
2
votes
1answer
398 views

Average the last N values in a Sheets column using Arrayformula

Experts, I need your help... I'm looking to create a formula (not necessarily an arrayformula), that will automatically calculate an average. This sounds easy, but surprisingly, it isn't. Let me ...
0
votes
2answers
211 views

How to filter after parsing CSV values contained in Google Sheets cells

I would like to be able to match all cells in column A (titles) that have one of the comma-separated values in column E (Key Search Terms). This will involve at least two steps. 1) Parse each of the ...
0
votes
1answer
872 views

Pull unique values from other sheets as new sheets keep getting added

I'm trying to get a unique list of values in a master sheet based on a list of values that appear in column A of other sheets. The tricky part is that every time a new column is added in the master ...
0
votes
1answer
15 views

How to add arrayformula function to this formula with IF function?

I made a table that has two columns (namd & State). The 4th column(check) makes conditional formatting possible for any of these combinations: (if name is either "Ali" or "John"...
0
votes
2answers
352 views

How do I "bin" results in Google Sheets?

I am trying to bin my results so my groupings have lower granularity - going from a 1-10 scale to a 1-5 scale. For example, any result 1 or 2 would be renamed to 1 in the next column, any result 3 or ...
0
votes
1answer
109 views

How to use arrayformula to find and get data from only a certain, dynamic set of columns?

I have a table where my headers are dynamically generated from another source and can show up with any header values, in any order. In the example below I've got the header values: AA - BB - CC - ...
0
votes
3answers
2k views

Can Weekday value be shown in natural (Su, Mo, Tu..) format in Array Formula?

For Google sheet with the following data in A2:B4 06/29/2019 1:25 PM 1 06/30/2019 1:32 PM 1 07/01/2019 1:40 PM 1 The output will show Monday 1 1 Saturday 1 1 Sunday 1 1 Can this be ...
2
votes
2answers
5k views

How to pull last value in a Google Sheets column and populate it across another worksheet column?

This is my first post here- I hope this is the right place for it, apologies in advance if it isn't. Context: I have a password protected presentation that I need to give users access to, but only ...
0
votes
2answers
70 views

In Google Sheets, analyze cell content, and if certain values are present, return a response in the next column

In the below example, I want to analyze each cell in column A, and if the cell's contents match certain parameters, return a value in column B accordingly. So if the value in a col A cell = apple, ...
1
vote
2answers
44 views

Spreadsheet formula to calculate most common items in ordered items lists

I am searching for a correct google spreadsheet formula to go through the list of ordered items and summarise the resulting winning items. Like is you have an Oscar ceremony and you get votes from ...
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
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 ...
0
votes
1answer
32 views

How to assign country based off list of either provinces or states?

I have a list of names and locations. Most of the locations include the name of the province or state. Some only include country names, some include state abbreviations. I also have a list of ...
0
votes
1answer
764 views

Remove blanks in import range of multiple arrays

I have an importrange formula as below which references multiple ranges, calling the sheet URL's using the 'L Links'!Bx, reference, each 'book' having an identical sheet set. (There are actually 90 ...
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 ...
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 ...
-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 ...
-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 ...
40
votes
5answers
35k views

Cumulative Sum without Script

For this question, I've created a Google Apps Script function, that calculates the cumulative sum of a range. Martin Hawksey on Google+ made a comment about a more efficient way of calculating the ...
0
votes
1answer
887 views

Using array formula in Google Sheet

Trying to make an ARRAYFORMULA that executes a query function which takes a reference to the current position of the row number and the specified column name. This is my version of formula: =...
1
vote
1answer
1k views

Autopopulate a RANGE of Cells from one sheet (tab) to another in a single workbook

All I want to do is have cells in one sheet (tab) in a workbook auto-populate with data and formatting of a different sheet (tab) in the same workbook. I know how to do this one cell at a time, BUT is ...
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
30 views

How do I insert different cell data from a table into a hyperlink?

I have a table of different cell data that I want to insert into different hyperlinks for each item: =HYPERLINK("https://www.otcmarkets.com/stock/CELL DATA TO BE INSERTED/security","SECURITY") Cell ...
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 ...
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
1answer
621 views

How to ignore certain strings when creating unique list of words in Google Sheets?

In Google Sheets, I am using the formula to create a unique list of words: =unique(transpose(split(ArrayFormula(concatenate(B2:E"+")),"+"))) But I want to filter out certain strings. My unique list ...
1
vote
2answers
298 views

How do I get the count number of weekdays in a datetime column in Google Sheets?

I have a timestamp in mm/dd/yy hh:mm:ss format from January to March. How do I count "how many days are Monday" in the range of A1:A199? (Or How Many are Tuesday, Wednesday, Saturday? This gives me a ...
0
votes
1answer
571 views

Linking a dynamic inventory price list to another file in Google Sheets

I've got a master inventory price sheet for all the ingredients we use in our recipes that I want to reference in all our recipe files to dynamically calculate costs. This way if we change suppliers ...
0
votes
1answer
23 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 ...
0
votes
1answer
32 views

Cross Comparing values between two columns for voting purposes

The current content of our Google sheet, is formatted as such: - PERSON 1 | PERSON 2 | YES - PERSON 1 | PERSON 3 | YES - PERSON 2 | PERSON 1 | YES - PERSON 2 | PERSON 3 | YES - PERSON 3 | PERSON 1 | ...
0
votes
1answer
147 views

How to use FLATTEN function in query function

Below in my query result: In the above table "Form Response" table is the response received in Google Sheets from Google Form. I want to convert "Form Response" table to "...
0
votes
2answers
37 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
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
1answer
42 views

Value transformation in Google Sheets

I'm working with a table of data as following: User ID: Event name: 111 Event 1 222 Event 2 333 Event 3 111 Event 4 444 Event 5 111 ...
0
votes
2answers
50 views

Update each cell in column from upper right adjacent cell

How can I make the start mileage in each cell update from the end mileage in the upper right adjacent cell? Preferably using something like a single array formula that can be updated. I think I'm ...

1
2 3 4 5
14