Questions tagged [google-sheets-arrayformula]
Use for questions about the use of the Google Sheets built-in function ARRAYFORMULA.
561
questions
0
votes
0answers
10 views
How to convert multiple form responses into easily readible table to use
I have a Google Form that is going to be used by different employees to add stock received. Since there is usually more than 1 item being received, I needed to provide space for them to add multiple ...
0
votes
0answers
12 views
How to get the previous edit of a cell in another cell using formula or script
I'm trying to figure out a way to bring the previous edit of a particular cell in another cell. I cannot use version history because every cell may e edited at a different time in different versions. ...
0
votes
3answers
45 views
=IMPORTHTML forces content to date format
I am a beginner with code and formulas here, so I was wondering if anyone could do exactly what was done on this question:
Trying to use Google Sheets importHTML() to import a table. It forces content ...
1
vote
1answer
21 views
Replacing items in comma delimited cell with same string each time
I'm trying to create a formula that will replace any contents of comma delimited cell with single string. The number of items in delimited cell will vary...
Specifically:
524352435,2345235523,...
2
votes
2answers
64 views
How do I find the max value in a column only if it contains certain texts in google sheets?
The data has numbers in text format and texts with numbers.
How do I find the max of the numbers in text format without converting to numbers?
How do I find the max if it contains certain texts (ie, ...
0
votes
1answer
17 views
Is there away on google sheets to paste text and automaticly create new rows with every line break or etc?
Is there away on google sheets to paste text and automaticly create new rows with every line break or ect?
I will paste a lot of text by this format in the example below (700 lines in total):
...
0
votes
1answer
21 views
How do I include blank cells when using an Array Formula?
I have a Google form that I'm using to collect info for user account creations. I wanted to make it so that the person filling out could just let me know about multiple users at an office in one entry ...
0
votes
2answers
55 views
Google sheets, convert value from 1 column to another based on particular data in it
Let's say I have those values in the column:
w 1
w 2
w 3
I want to convert those values to
1
2
3
in another column.
So basically I need to convert the value "w x" to "x".
How ...
1
vote
1answer
35 views
How do you perform a bulk find & replace using a list of search phrases and their replacements?
This is not my wheelhouse and I'm realizing I'm in a bit over my head. Any help would be greatly appreciated.
What I'm trying to accomplish
https://docs.google.com/spreadsheets/d/...
1
vote
1answer
90 views
Search multiple columns for one value
This is roughly what my table looks like:
A B C D E F
Name |Ingredient 1 |Ingredient 2 |Ingredient 3 |Ingredient 4 |...
0
votes
2answers
38 views
Google sheets array formula for taking average every Sunday and output is in same row corresponding column
I would like to take the average using an array of every 7 days on a Sunday and place the output on the same row but corresponding column of that Sunday.
If a cell in B is left blank then it should ...
1
vote
1answer
27 views
Is it possible arrayformula combined with many if, and, isblank?
Is it possible arrayformula combined with many if, and, isblank ??
The code below works fine
=IF(AND(ISBLANK(F8)=TRUE,ISBLANK(H8)=FALSE), "Not shipped", IF((AND(ISBLANK(F8)=FALSE,ISBLANK(H8)=...
0
votes
1answer
18 views
Calculating monthly average from daily average (grouped by month)
I have a spreadsheet with three columns: one for dates, one for daily income and one for daily change rates.
I want to have a sum of all the daily income grouped by month as seen above. For that, on ...
0
votes
1answer
39 views
Removing values in array without filter() in Google Sheets
I want to remove specific (or any) values in dynamic array/range and return the result of the resized array without those values without using filter() as I need to use arrayformula.
For example,
{&...
0
votes
1answer
26 views
How to apply DATEVALUE to column
I have two columns with extracted date values. I want to add those two values together and apply to the whole column. The data looks like this:
AM AN AO AP AQ
date time ...
0
votes
1answer
28 views
Stacking IF Statements
I am trying to pull data from another sheet based on selections made from multiple dropdown boxes. For example... If column 1 text reads RAZA pull data, if column 1 reads Standard pull data. So on and ...
1
vote
1answer
33 views
Expand Countifs Arrayformula in rows and columns
In this google sheet
I have an array of Types and floors, The criteria rows and headers are auto populated by unique formulas.
I'm trying to create an array formula which will expand in the width (...
0
votes
0answers
14 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
26 views
Referencing a calculated value in another calculated value
Here's the general structure of my spreadsheet:
Start End Count Duration Hours Count/Hour
09:00 10:00 10 01:00 1.00 10
Duration = End - Start
Hours = TIMEVALUE(Duration) * 24
Count/...
1
vote
1answer
24 views
How to create a link with content bound to other cell's content in Google sheets?
As stated in the tittle I'm trying to make the content of a cell that links to an other cell have it's display text be based on the other cell's content. Before I was able to do it by right clicking ...
0
votes
1answer
38 views
Using REGEXEXTRACT to retrieve specific content from two separate rows in the same column
I Included a Google Sheets link
=REGEXEXTRACT(A2:A3, "19..")
To me, it would seem as if this would check both cells for any date starting with 19 and extract that into the cell
But, it ...
1
vote
0answers
25 views
Array formula to extract N non blank cell in Row 1-16
I have a google form where team coaches input which players played on a given day.
They also input if they were Man of the Match, Yellow/Red Cards Received, and how many Goals they scored.
When the ...
0
votes
1answer
53 views
How do I automatically match text responses to numbers and sum those numbers in Google Sheets/Forms?
I am using a google form to collect responses from students. For each student I would like to match those text responses to specific numbers. Never = 0, Almost Never = 1, etc. Then sum the results for ...
0
votes
2answers
51 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, ...
0
votes
1answer
25 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
36 views
Google Sheet - Count Random Average
Number Of Invoice | From | To | Total
10 | $25 | $100 | ?
I want the Total column to fill with the average number of 10 invoice with each invoice random between the ...
1
vote
2answers
53 views
G sheet How to jump over no value cells with SUMIF function
I would to get the sum of the duration of the column B
+---+----------+
| A | 14:45:00 |
+---+----------+
| A | #Value! |
+---+----------+
| A | 13:34:50 |
+---+----------+
| B | 23:41:00 |
+---+-----...
0
votes
1answer
21 views
ERROR IN QUERY FORMULA GOOGLE SHEETS
I need to join two worksheets from different files in another one that already exist in Google Sheets
I tried to do this:
=QUERY({IMPORTRANGE("1GoKFiJjBKcGQGGi6JMvk9xD5TIypsNSmpLCTeadWQnU",&...
0
votes
2answers
104 views
Restructure (flatten) data of Google Sheets table in a new table
Column A of my Google Spreadsheet contains dates, columns B to G contain numeric values, line 1 contains the header names. Due to automatic updates the line count varies, but the column count is ...
1
vote
2answers
59 views
Extracting text from a capture group while using an arrayformula to consider multiple criteria
I have a page which contains a list of regular expressions in the format "some text (thing I want) but only if (thing is matched here)" that are fairly varied. There are 29 of them so far, ...
2
votes
2answers
54 views
Calculate Total DateDif Average Between 2 Columns (start, end) according to year expressed in days, hours, minutes
I'm having trouble getting a formula to work to calculate the total average time between times (start and end).
Sheet: https://docs.google.com/spreadsheets/d/...
0
votes
1answer
32 views
Average of cell range from string
In Google Sheets I have a string like this: "G4;G15;G26;G37;G48".
How do I get the average of the values in those cells?
I'm sure there's some arrayformula and indirect to be used, but I can'...
1
vote
1answer
66 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
0answers
28 views
Arrayformula to Query Sum
Good day,
I am trying to run a query that returns the sum of a column based on the query key and have it displayed as an array so that each sum is visible. With my below formula, I am successful.
```=...
0
votes
1answer
12 views
Show image if rank equals number
I have a row of 5 cells. I want to show an image above each cell depending on the rank of the cell. So if the rank of the cell equals 1, show a specific image (from a link), if the rank is 2, show a ...
0
votes
1answer
22 views
Create Columns via Formula for two dates
How do you do this with columns instead of rows?
=ARRAYFORMULA(TEXT(ROW(INDIRECT("A"&DATEVALUE("2018-4-13")&
":A"&DATEVALUE(&...
1
vote
0answers
31 views
How do I calculate subtotals by using ArrayFormula and without formula in the cell itself in a Google Sheet
I'm working on an estimate sheet, got it all worked out pretty neatly
and trying to tackle the formulae, make them work from the header rows using ArrayFormula instead of having each cell holding ...
0
votes
1answer
22 views
How can I include parameters in Google Sheet?
I have a query string to extract data from my master sheet, which looks like this:
=QUERY(IMPORTRANGE("1ABCsxIDRXU-k6YXWJ47Awjk4757VsMFBmye7rkBC123","Master!$A$1:$H"),"SELECT * **WHERE Col2 = 'abc123'...
1
vote
1answer
70 views
Listing and formatting tagged data
I'm creating a list of texts with some tags associated to them in Google Sheets, each text has a "tags" column where the tags are separated by commas.
I want to have a second sheet where the ...
0
votes
1answer
46 views
ARRAYFORMULA and FILTER
The following sheet is an example of what I'm trying to do : https://docs.google.com/spreadsheets/d/1B9a8OUvwciCAYURLZq-Jd2KeSUZsYlalAFNsANzRzNw/edit?usp=sharing
In the first sheet ("Matrix"), I have ...
1
vote
1answer
48 views
Convert string to real date time value?
Here is my current input:
14.05.20
12.37.01
And yes, as it shows, there is a line break in the cell...
Here is example sheet: https://docs.google.com/spreadsheets/d/...
1
vote
1answer
114 views
Find the last instance of multiple values
I really need some help with this.
I need to search for the value of A2: B2 in another tab in google sheets and it needs to return the row number of last occurrence/entry of the values in A2: B2 in ...
0
votes
1answer
21 views
How to replace or substitute one column of data with their corresponding value from a list
I'm trying to replace a column of data (Prodct SKU) on one sheet (a) with the corresponding value taken from 2 columns on sheet (b) (Product SKU & Product ID).
I've attached the spreadsheet to ...
2
votes
1answer
21 views
How to autofill specific cell when any next cell is filled
+------------+---------+---------+
| Date | Col1 | Col2 |
+------------+---------+---------+
| 2018-01-01 | Value 1 | Value 1 |
| 2018-01-01 | Value 2 | Value 2 |
| 2018-01-01 | Value 3 | ...
1
vote
1answer
44 views
Trying to pull Duplicate data from multiple columns
https://docs.google.com/spreadsheets/d/1VSiKZbD_PoQTfbav7spalkyBgrt4T8juaLlKOM7LgLM/edit#gid=0
I have two columns, A and B. Throughout this project, the same name will appear in each column. I want ...
2
votes
1answer
32 views
How do I get a formula copied to all cells below it in the entire column and increment by x instead of manually dragging it down to eternity?
If in Cell A2 I have the formula,
=IF(MOD(ROW(),1)=0,A2+1,A2)
how do I get it copied to all the cells below in the entire column (e.g. 900'000 cells) instead of manually dragging it down to ...
1
vote
1answer
50 views
Formula to find the cell in column C where a value in column A = a value in column E
I'm trying to create a formula in a google sheet to find the value in the C column where the value in the A column is equal to the value in the E column.
E.g. Column E = 1 in Row 9-16 and Column A = ...
2
votes
1answer
32 views
Array formula to show a leading zero
I have a Google Sheet where an array formula in Column I creates a video name based on the content entered into columns A+B+C+D.
Column C is the number of the video. For some videos, I need a leading ...
3
votes
1answer
27 views
How to SUM LARGE 5 largest elements out of 8 numbers in a row
In Excel I am able to get the output using this formula:
=SUM(LARGE(C5:L5,{1,2,3,4,5}))
But in Google Sheets I am unable to get the right answer. Can anyone please help?
In the image below, ...
1
vote
1answer
34 views
Array formula with concantenate not working
I have a Google Sheet where I am wanting to use an array formula so it auto copies down the column rather than the current formula which needs to be copied down manually.
=(IF($E2="Yes", $D2, ...
