Questions tagged [google-sheets-arrayformula]
Use for questions about the use of the Google Sheets built-in function ARRAYFORMULA.
71
questions
42
votes
6
answers
36k
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 ...
- 22.8k
2
votes
5
answers
2k
views
multiple IF statements with between number ranges alternative
How to efectively deal with multiple IF-statements and with multiple "between number" ranges in Google Sheets' spreadsheet (e.g. C column based on B column)
with a rule set as follows:
01-10
11-20
...
- 26.2k
3
votes
3
answers
33k
views
Google Sheets QUERY won't display cell text if other cells have numbers
I'm not sure what is happening in my Google Sheet query and could really use some help. I have a quick screencast that demonstrates and explains the issue.
Basically, I'm using a query to pull data ...
- 2,857
2
votes
2
answers
2k
views
Google Sheets Split row in multiple rows
I'm doing a spreadsheet for an event and I need to split a row into multiple rows. It's getting data from a form.
I need a script that can arrange the data like in the blue rectangle.
2
votes
1
answer
371
views
Formula breaks subtly in ARRAYFORMULA in Google Sheets
I have a spreadsheet document with a formula comparing a list of URLs to lists on two other sheets within the document.
I desire to know if each URL on the first list is present on the blocklist, ...
- 23
14
votes
3
answers
39k
views
Can you do a UNION SELECT in a Google Sheet?
I've got a Google Sheet linked to a form which collects replies from three different people on the same subject using the same form and editing three times. This creates a sheet which contains three ...
- 187
10
votes
6
answers
30k
views
Split content of one row into multiple rows?
I have a table where each row contains data like this:
Team name | First member | Second member | Third member | Team description
Would it be possible to transform/break this data so that each row ...
- 1,864
8
votes
7
answers
39k
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....
- 197
6
votes
2
answers
3k
views
Fill zeros for the dates when no data was recorded
I have a table with dates (in increasing order) and corresponding values. Some dates are missing, like 3/27 below. I'd like to insert them in Date column, and have 0 entered in the Value column for ...
user79865
1
vote
1
answer
3k
views
ArrayFormula to compute running average for groups of rows
Is it possible to write an array formula that calculates the running average of the Amount column for each of the Name groups in the following sheet?
The rows are sorted by Name. In Column C, I have ...
- 13
36
votes
9
answers
256k
views
How to Fill a Column with Sequential Dates in Google Sheets
In Excel, you can right-click drag a date downwards to fill a column with the next dates in the sequence. For example, if you enter 9/1/2014, select that cell, then right-click drag down over the ...
- 521
14
votes
3
answers
67k
views
How can I create a unique list of data across multiple rows AND columns?
Google Sheets has a UNIQUE() function which when given a column of values, will output a column of all unique values.
This works well for column-specific data, however, you have multiple columns and ...
- 297
6
votes
2
answers
6k
views
Add missing dates to list of dates with values
Current Data:
9/20/2018 6
9/22/2018 3
9/23/2018 1
Desired Output:
9/20/2018 6
9/21/2018
9/22/2018 3
9/23/2018 1
I would like to have the missing dates added to Column A while the ...
3
votes
1
answer
3k
views
Adding missing dates to Google Sheets
I'm pulling data from HubSpot to Google Sheets via 3rd party connector. I have a raw data set on Sheet 1 and then I'm pulling certain data from Sheet 1 to other Sheets with QUERY function.
For ...
- 31
1
vote
1
answer
4k
views
Using SUMPRODUCT with ARRAYFORMULA
I have the following random data in sheet one
A orange
D yellow
C yellow
B orange
B orange
C blue
A yellow
D blue
C orange
C blue
In sheet two I use the following query function:
A1=...
- 11
1
vote
1
answer
9k
views
How to combine two QUERY results into one column
I have the following QUERY
=query(XX!A2:A;" select A where A>'50' ")
and
=query(YY!A2:A;" select A where A>'50' ")
how do I combine these two data sources so I don't have to have two ...
- 183
0
votes
2
answers
707
views
Average of only the numeric part of cell values when some of the cells contain text and numeric values
I am trying to figure out a formula that will allow me to find the average of a group of cells, where some of the cells only contain numeric values, and some cells contain both numeric values and text....
- 35
147
votes
3
answers
146k
views
How to paste a comma separated string over multiple cells in Google Sheets?
Say I have a string of: l,f,x,a,s,f
I would want to paste this every comma-separated value over 6 adjacent cells. How would I do it?
- 1,587
12
votes
4
answers
4k
views
Average of last 7 non-empty non-blank cells in Google Sheets
I have numbers in range D7:D and there are times when some cells are empty. I want the average of the last 7 numbers but to skip blanks. So if in the last 7 there's only 3 that are full, I want it to ...
11
votes
2
answers
13k
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, ...
- 111
10
votes
5
answers
45k
views
Count the number of words in a string in Google Sheets
I have a simple Google spreadsheet with a number of text strings. I just want the number of words contained in each cell. Is there an in-built Google spreadsheet function I may use?
Nishant
7
votes
2
answers
12k
views
Query Multiple Ranges with Results in Row
I am trying to query five ranges with the results displayed across a single row. To do this, I was using { } with commas separating each range, but it's only giving me the first range in return.
...
- 317
7
votes
3
answers
3k
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 / ...
- 81
4
votes
5
answers
12k
views
Auto-fill date function changes the YEAR and not the day
I'm trying to get Google Sheets to auto-fill dates in the beneath cells after I have manually filled one myself. The strange issue is that when I hold Command key and drag down, the cells auto-...
3
votes
1
answer
2k
views
Unique Values From a Matrix
I have a spreadsheet that looks like this:
Column A | Column B
| An unimportant value
1 | Value 1, Value 2
2 |
3 | Value 3
4 | Value 3, Value 1
| Don't ...
3
votes
1
answer
7k
views
How to use custom function with arrayformula?
I'd like to use a Google Apps Script custom function in conjunction with arrayFormula so this may be applied to a range. How do I achieve this?
Let's say I have wrote a function that will return ...
- 579
3
votes
1
answer
2k
views
Google Sheets - Using IMPORTRANGE with a variable number of rows
Google Sheets - Using IMPORTRANGE with a variable number of rows
...is it possible to add the required number of blank rows to avoid the IMPORTRANGE's overwrite error?
Test Input: https://docs....
- 33
3
votes
1
answer
575
views
Print incremental numbers in Google Sheets
Cell A1 holds a numerical value (e.g. 10), and I'd like to output the list of numbers from 1 to that number in B1:
What's the formula I need for B1? It seems trivial, and I know it probably requires ...
- 133
2
votes
1
answer
355
views
How to create no pop-up Hyperlinks/Bookmarks for an array of cells within the same tab/sheet
I'm trying to create a formula that will allow all the cells in Column B to include a hyperlink to cells in Column J in their corresponding rows. The kicker is that I'm trying to keep the current ...
- 45
2
votes
1
answer
90
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 ...
- 57
2
votes
1
answer
2k
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 ...
- 489
2
votes
3
answers
2k
views
Find the closest value within a range of cells and output cell reference
There are values from F column to AJ column in row 30. My input would be available in C column in row 30. As given in the picture, my input would be 118.45, I would like to find the closest value to ...
- 25
2
votes
2
answers
5k
views
Google Sheets Query with Date and Time
I need help with a query. I need to filter on some Google Form data and only show the results that have been submitted AFTER a specific timestamp.
Timestamp Cell = J2 - This cell is in the following ...
- 133
2
votes
1
answer
3k
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 ...
- 135
2
votes
1
answer
68
views
Comparing Unique Values to a Range
I have the following formula
=CONCATENATE (FILTER(boxmod!AC2:AC, boxmod!I2:AB=concatboxmod!C2 ))
The intention of this formula is to take a range and compare it to a list of unique identifiers on ...
- 172
2
votes
2
answers
569
views
Google Sheets' Duration Input issue
We have been using Apple Numbers which seems to have this duration thing figured out a little better than Google, (either that or I'm missing something). In Apple Numbers, if I type into a cell with a ...
1
vote
2
answers
440
views
Remove Count column by repeating rows in Google Sheets
I have a worksheet, 'wi', in a Google Sheet similar to the following example but with an undetermined number of rows:
Count | Entry | Description
-------|-------|-----------------
2 | ...
- 163
1
vote
4
answers
16k
views
How Can I Sort a Data Range in Google Sheets Based on an Data Range?
I have been looking for a way to sort this information. I need to sort these items in this order: BK, BB, BV, BIV, BIII, BII, BR. I was trying to make a Google Script to get it, but I could only ...
- 25
1
vote
1
answer
441
views
Query/Filter function finding the closest date that is less than the order date for an array of dates using a given variable
Overview
I need to get the cost of something by comparing item dates against an order date for one or more of the same item with different item dates, and making sure to select the item date that is ...
- 25
1
vote
1
answer
704
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 ...
- 35
1
vote
1
answer
1k
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 ...
- 121
1
vote
1
answer
78
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 = ...
- 57
1
vote
1
answer
132
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 - ...
- 11
1
vote
1
answer
68
views
Help Sorting Data
I have a spreadsheet that I'm using to keep track of stats for players in a league. This is the link:
https://docs.google.com/spreadsheets/d/193GxNzaX4-bGrNB8eT4vhqAOKhUrChRDgq-HYVU_Txk/edit#gid=...
- 11
1
vote
2
answers
176
views
Find and place in a column, the first non-blank cell in each row from a range of columns
I'm attempting to create an ARRAYFORMULA that can extract the first non-blank cell value on each row in a range of columns.
My latest attempt is this:
=TRANSPOSE(QUERY(TRANSPOSE( A2:D6),"select * ...
- 147
1
vote
1
answer
49
views
Analyse multiple columns per row
I need to combine the data of three different columns. Thus, I have this:
-------------------------------------------------------
| ID | ID Data | Markdown 1 | Markdown 2 | Markdown 3 |
--------------...
- 13
1
vote
1
answer
6k
views
Concatenating URL with a value to create link, but links only partially work
I have a column of cells in Google spreadsheet with values such as:
b2397
b9504
b3333
b8655
bxxxx
I would like to combine each value with a URL, for example:
http://websitex.com?id=
...
1
vote
1
answer
274
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 ...
- 123
1
vote
1
answer
76
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)=...
- 13
1
vote
1
answer
132
views
How to automatically apply a hyperlink formula with relative reference to a whole column?
I have the following formula in every cell in my C column:
=IF(ISBLANK(B4),"",CONCATENATE("https://www.url.com/",B4))
If the cell in the B column has content, it takes that content and concatenates ...
- 13