Questions tagged [google-sheets-arrayformula]
Use for questions about the use of the Google Sheets built-in function ARRAYFORMULA.
0
votes
0answers
17 views
Change an entire google sheets to UPPER case, not with an add-on or onEdit(e)
I need to change all the text in a google sheets to upper case, the problem is it's filled in by Zapier which doesn't register as an edit for onEdit functions. It's also automatically converted to ....
0
votes
2answers
16 views
How to use an ArrayFormula containing a partial range
I have a Google Sheet that is populated by a linked Google Form. I've added some extra columns to calculate extra values for each row that gets added. I want these columns to be automatically ...
0
votes
0answers
15 views
Is there a way that I can flag in Google Sheets when multiple cells contain the same values?
I'm wondering if someone can help me in my battle with Google Sheets. I'm creating a rota and I want it to flag if all senior members of staff are either off or all on the same shift (either Early or ...
0
votes
0answers
15 views
How do I interleave rows of data in Google Sheets or Excel
I have data on several different worksheets and I want to combine into single sheet. The data is text, not numeric. I can do this work in either Google Sheets or Excel.
I have:
Sheet1 R1
Sheet1 ...
0
votes
1answer
39 views
Set formula into a cell based on content of another cell?
Google Sheets:
I would like to set a formula into a cell, but this should depend on content of another cell.
Example Google Sheet (anybody can edit):
https://docs.google.com/spreadsheets/d/...
0
votes
1answer
23 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
32 views
Arrayformula and Sum queries. Possible alternatives
I am using a sheet to list and summarise teachers absences and substitutions. Here's the worksheet
Basically in the sheet "Substitutions" all absences and substituions are stored record by record:
...
0
votes
1answer
16 views
Inserting a row in Google Sheets does not copy the formula present in other rows
I am using Google sheets withe the following formula in one of the columns
=ARRAYFORMULA(IF(ISBLANK(M2), "", ROUND(M2/G2, 2)))
Whenever I add a new row anywhere in the sheet, the formula does not ...
0
votes
0answers
24 views
Use arrayformula to allow vertical formulas to expand horizontally?
The following sheet (here) is using a vertical-range formula on some cells to calculate averages (vertically) every 3 rows.
Once the end user will insert a new column, the newly created cells will ...
0
votes
1answer
23 views
Count unique tags
I’m trying to list and count all tags in my table. For some reason, anything after comma, is getting a white space before the word. So if the tag is found on the beginning of a record, it is seen as ...
1
vote
2answers
36 views
Using multiple AND conditions in one IFS fromula
I’m trying to make sheets calculate my delivery times based on which locations the items are shipped between.
Tried combining the Ifs and AND formulas, but I keep getting errors.
(Let’s say the ...
0
votes
1answer
16 views
Sum a quantity list in an array
I have an array table using ARRAYFORMULA({Fruit Names, Quantity}) that looks like
| Apples | 2 |
| Oranges | 3 |
| Bananas | 5 |
| Oranges | -2 |
| Bananas | -1 |
| Apples | 1 |
I'd like to ...
0
votes
0answers
37 views
Google Sheets Combining multiple IF statements into one formula (as an Array)
Hope someone can help, got the formula below but I want to modify it so that the first IF formula, IF(F3:F="Delay","Delay", returns a “Delay” if it finds a match (not if it finds "Delay") and if it ...
0
votes
1answer
211 views
How to concatenate columns for multiple rows simultaneously in Google Sheets?
I have the following 3 rows of data in cells A1:A3 in Google Sheets:
Doe, Jane
Doe, Joe
John Doe
I want the following in B1:B3:
Jane Doe
Joe Doe
John Doe
Essentially, I'm creating a new column ...
2
votes
1answer
51 views
SUM a range of cells (sometimes string, sometimes numbers), parsing the cell if it is a string
I am trying to get the sum of a column of cells, which sometimes contains numbers and sometimes contains a string with numbers in it. I've found that the formula
=arrayformula(sum(0+iferror(...
1
vote
0answers
22 views
How to create a user defined function that maps one value to another in Google Sheets
I would like to create a mapping function that takes as an input 3 arrays of the same length and a double between 0 and 1.
Array #1: Each element is the minimum value of the nth bin.
Array #2: Each ...
0
votes
0answers
24 views
Convert column with multiple subheadings to sortable table in Google Sheets
How can I convert a column with variable data like this:
| <h2>TITLE 1</h2> |
| <input ...>Item A</input> |
| <input ...>Item B</input> |
| &...
0
votes
1answer
147 views
ARRAYFORMULA for a column, but only for blank cells
I'm trying to fill a formula down a column using ARRAYFORMULA. However, there are certain cells in this column which I would like to overwrite, but all the other cells should use the ARRAYFORMULA.
Is ...
1
vote
1answer
325 views
Dynamic updating referenced cell from another workbook after adding/deleting rows using IMPORTRANGE - use ARRAY or VLOOKUP?
I add/deleted/move rows frequently in my workbooks. When I add/remove rows from Spreadsheet#1 ("S1"), I need the referenced cell in Spreadsheet#2 ("S2") to dynamically update so it still references ...
0
votes
1answer
23 views
Split rows dynamically based on row length (COUNTA) and transpose to 2D array
I have data in this format:
And I need it to be reformatted into this:
Basically, each row in the initial format is composed of 4 concatenated sections of equal lengths: Part(s), Product(s), Quant(s)...
1
vote
2answers
1k views
Google sheets function IF, AND conditional formatting
EDIT 2: link to test sheet: https://docs.google.com/spreadsheets/d/1IWQWDK9piw_xFVm_48XZ8YAS_LQqXi6sd2GITLIfoAQ/edit#gid=903221057
EDIT 1: added a column, so now J=K, K=L, L=M | updated formulas to ...
0
votes
0answers
78 views
Array Formula Auto-Calculation in Google Forms
I have a google form which is returning data into Column A and Column B and I need to perform calculations on that in Column C, D & E. I have been using Array Formula so that C & D auto-...
0
votes
1answer
18 views
How to summary the multiple entry into a single sheet by name and by date. Name is in Row and Dates are is in column
Sheet 1:
Name 1-Jun-18 2-Jun-18 3-Jun-18.......
KB CL
KB EL
AJ SL
SD SL
SD CL
I need this data to be like below table in Sheet 2: ...
0
votes
1answer
137 views
Google Sheets Arrayformula with exceptions?
I am using fantastic Arrayformula to populate a grid in a Google Sheet:
The following is being used in each column: =arrayformula(if(Z2:Z <> "","■",""))
Now there are some cases where the ...
0
votes
1answer
474 views
Google Sheets Arrayformula on both horizontal & vertical cells?
In a sheet, I make calculations horizontally, however I can't build the arrayformula to expand calculation vertically on each row.
See the following sheet:
https://docs.google.com/spreadsheets/d/...
0
votes
0answers
76 views
Arrayformula concatenate both vertical and horizontal
I'm stuck on trying to solve this google spreadsheet issue. I have an table with for example countries and then details. What I would like to do is create a table automatically that concatenates all ...
0
votes
1answer
190 views
Google Sheets - Fetch random value from array for each new line created with form
I have a Google Form that fills up a new line with values in Sheet1!, column A and column B.
I am trying to create an =ARRAYFORMULA function in column C, which will populate the C cell in each new ...
0
votes
1answer
202 views
Google Spreadshheet Countif based on multiple criteria
For reference here's the sheet:
https://docs.google.com/spreadsheets/d/1CS-fYhqeKRmEiJC5XA6kRIwIjyBDlBcXj-K9FHjCZjA/edit?usp=sharing
I am trying to create a filter that shows me all the data for May ...
0
votes
0answers
22 views
Google sheets: convert ragged array for to 2D array
I have multiple columns in a single row in a ragged array and want to convert this to a 2d array.
This example can be found here
I realize that similar questions have been asked for multiple entries ...
1
vote
2answers
62 views
is possibile to concatenate with bidimensional arrayformula?
I have a table like this:
+----------+----------+----------+----------+
| Photo1-1 | Photo1-2 | Photo1-3 | |
+----------+----------+----------+----------+
| Photo2-1 | Photo2-2 | | ...
1
vote
1answer
23 views
How to show which column has the first amount and count columns to reach a sum amount
I am working on a Google sheet that shows a sum that has been advanced to a customer and then shows the income per month the customer receives. We will retain 100% of the income until the amount that ...
1
vote
0answers
15 views
Multiple IF/And as array in Google Sheets [duplicate]
I am attempting to create an array that will compare the contents of two cells and return a resultant text to a third cell. I am able to make it work as a formula in a single cell, but when I attempt ...
2
votes
1answer
221 views
Refer to the cell directly to the left of the cell with the maximal value in a range
I've got a spreadsheet with about 5,000 rows and roughly 50 columns. The columns are alternating between terms and numeric values associated with the terms (example: baseball / 1000 / football / 500 / ...
0
votes
1answer
347 views
Vlookup across multiple columns
This question is related to the same project in this question, but I'm now at a later step.
I have a Google Sheet with a variety of German translations of a large set of English words. Using a ...
4
votes
1answer
2k views
Google Sheets: Is there kind of map function?
I don't like expanding formulas. First, when I modify them, I have to copy them again, it is like copy&paste programming. Second, the I'd like them to match the original range, which might change. ...
1
vote
1answer
69 views
Consecutive numbering skiping empty cells with ArrayFormula
I am trying to auto-number only the existing names on a cell using an arrayformula:
I have two cells one for the names and the other for consecutive numbers.
Here I have an example of what I want to ...
2
votes
1answer
280 views
Does the ArrayFormula formula work with Concatenate?
I am trying to make an ArrayFormula that see if column D is empty or not
If it is not empty it will then take a number and concatenate to generate an url to show an img from a URL.
My current ...
0
votes
0answers
9 views
Get all states from a country in Google Analytics Addon using Google Spreadsheets
I need to get all states from Brazil in Google Spreadsheets and display them in a chart showing the unique visitors for each state. I know how to display data in the chart, I just don't know how to ...
0
votes
0answers
235 views
DATEDIF of latest date of a range (Google Spreadsheet)
I'm trying to get the latest date for a range of cells and Calculates the number of days between two dates using the function DATEDIF.
As you can see from my screenshot example, my calculation should ...
0
votes
0answers
1k views
How can I make a multiple criteria index,match array formula work?
I've been using array formulas in my google Sheets a lot, of the type:
={"Heading";arrayformula(if(len(A2:A)>0,FORMULA HERE,""))}
in order to "auto drag down" a formula when the A column is ...
3
votes
2answers
254 views
CountIf in upper cells with ArrayFormula
I have some values in the column A and in the column B want to calculate concatenation of the value and how many times it was used in rows above.
In the usual formula, I would've written in B2
=A2 &...
3
votes
1answer
79 views
Google Sheets Array Generation
Is it possible to generate an array of numbers between two provided numbers
eg =sequence(1,10) results in {1,2,3,4,5,6,7,8,9,10}
My end goals is to generate a list of days between two dates, but have ...
6
votes
1answer
1k views
How to use AVERAGE inside ARRAYFORMULA in Google Sheets
I want to use the ARRAYFORMULA version of
=AVERAGE(Linda!B3, Nandi!B3, David!B3, Remmelt!B3)
I want the cell to calculate the average value of the entries in that cell from the other tabs (Linda, ...
2
votes
1answer
234 views
How to sum all positive values in Google Sheets based on whether the associated data in another column is unique
I have a spreadsheet that my company is trying to transfer into Google Sheets from Excel. I've used an array formula to accomplish this result in Excel, but it's not translating to Sheets and I know ...
0
votes
1answer
1k views
Using Conditional Formatting to highlight data in new table that matches data in separate table
Sheets 1 thru 6 all have an Array Formula pulling data from other sheets. The Array Formula runs multiple Query formulas that skip over rows with missing cell data and then goes back to those rows and ...
4
votes
2answers
6k views
Stacking multiple query formulas throws “an Array Literal was missing values for one or more rows”
I am attempting to run multiple queries where it adds rows where if F is blank, then it skips those rows, then runs the query again and replaces row F with row G.
I have a much more complicated ...
4
votes
1answer
2k views
QUERY where column IS NOT EQUAL to ANY cell in column A of a second sheet
I want it to pull the max value from column F where the corresponding row has C matching the number 14, and A does not have any exact text matches in any cell in column A of a separate sheet.
I feel ...
5
votes
1answer
309 views
How to SUM a row of numbers after extracting numbers with REGEX?
I have columns of data that are of the form:
A1: "IDjohn / 35.00 / california"
A2: "IDmike / 25.00 / oregon"
A3: "IDrebecca / 40.00 / ohio"
B1: "IDchang / 20.00 / washington"
B2: "IDwill / 25.00 / ...
2
votes
1answer
35 views
Arrayformula using cells as parameters
I would like to use the ARRAYFORMULA combined with two cells as range. For example:
Cell A1 value: A3
Cell A2 value: B7
Resulting formula: ARRAYFORMULA(A3:B7)
Is it possible? How can I do?
2
votes
1answer
176 views
Array formula with running total that restarts based on values in another column
I have tried to create a running total with some kind of filter as it were and have been unable to figure out how to do it. I have seen people do a running total using MMULT but not sure how to adapt ...
