Questions tagged [google-sheets-arrayformula]
Use for questions about the use of the Google Sheets built-in function ARRAYFORMULA.
806
questions
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
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
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
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
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
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
10
votes
1
answer
21k
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. ...
- 229
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
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
7
votes
2
answers
35k
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 ...
- 135
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
6
votes
3
answers
30k
views
Convert Text String into Date/Time Value
I'm fetching data into a Google Sheet. The date column contains multiple cells with the following text string pattern:
Mon Mar 11 19:05:32 +0000 2019
The pattern always repeat itself:
[day] [month] ...
- 772
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
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 ...
5
votes
2
answers
1k
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 &...
- 153
5
votes
2
answers
9k
views
Filter() - referencing source array in criteria
The filter() function looks through a source array, and filters it according to condition
=FILTER(A:A,ISNUMBER(A:A)).
My source array is a rather long formula, so I end up repeating the same formula ...
- 73
4
votes
2
answers
24k
views
COUNTIFS with multiple OR
I have two sheets, one is a dump sheet of values like below:
and the second sheet is using values from this to the total.
I am trying to get the total count for the corresponding zone and test name ...
- 41
4
votes
2
answers
13k
views
Summing top three values in a column from a Google sheet
I have a Google Sheet with a variable number of data rows. I would like to sum the three highest values from a certain column, but they won't necessarily be in the top three cells. An empty cell ...
- 1,222
4
votes
3
answers
4k
views
How can I use ARRAYFORMULA (or something similar) with GOOGLETRANSLATE?
I'm learning a new language and I recently discovered Google Sheets' GOOGLETRANSLATE function. I thought it'd be neat to have a personal dictionary of translations which I can even annotate. So I ...
- 151
4
votes
1
answer
203
views
Rearrange Name Format in Google Sheets using one formula only
Is there a simple way to do this using only one arrayed formula?
Here are the names listed in the above image:
NAME
dela Cruz, Antonio Karl Jr.
Woods, Oliver
Hasegawa, Ken Sr.
Sta. Maria, ...
- 65
4
votes
5
answers
27k
views
How automatically to add spaces inbetween each character in a text string?
Teachers I work with, enter four scores for a student’s assessment in a single cell, like this: 2113, with no breaks or spaces.
How can I separate those characters so as to be able to create a chart ...
- 41
4
votes
3
answers
11k
views
Google sheet, images disappear randomly
Using Google Sheets I'm trying to populate a list of countries with (among others) names, ISO-alfa2 code, and flags. I use this IMAGE() and ArrayFormula() to get the flags from URL in my private ...
- 71
4
votes
2
answers
10k
views
Escape single quote in cell content in a QUERY
My Problem
I have a Google Spreadsheet with two tabs - one containing regexes and values:
And the other containing phrases which matches regex in the first tab, and a QUERY Formula that picks the ...
- 1,793
4
votes
1
answer
13k
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 ...
- 135
4
votes
2
answers
6k
views
ISBLANK function returning unexpected results
I want to show the average of a range if any cell is populated.
However, my formula shows the average only if the first cell is populated.
I'm using the below formula:
=IF(ARRAYFORMULA(ISBLANK(A2:C2)),...
- 143
4
votes
1
answer
2k
views
Formula to find the second to last (or nth to last) cell with value ignoring empty rows/cells
I have a spreadsheet that I used to run in Excel but due to other family members needing access to it I've moved it over to Sheets.
Anyway, I have the following formula that was running in Excel that ...
- 43
4
votes
4
answers
892
views
Joining two rows of data with alternating delimiters
I'm working with two rows of values that are related to each other and trying to build a formula that will compile the data into one cell, with syntax that uses two delimiters.
If a cell in the ...
- 51
4
votes
1
answer
1k
views
Apply a formula to each cell in column before computing a sum of the cells
How could I apply a formula to each cell in a column before computing a sum of the cells in Google spreadsheet?
In other words, I have a column of cells with numbers. And what I want is to iterate ...
4
votes
1
answer
2k
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, ...
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
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
2
answers
297
views
cannot drag query across multiple rows (overwrites data)
Is it possible to click and drag queries?
I describe my problem below and also have a reference sheet.
cells A1:B5
Category
Withdrawal
CatA
4
CatB
2
CatA
6
CatB
11
cells C1:C3
Check For
CatA
...
- 279
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
3
votes
1
answer
576
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
3
votes
1
answer
17k
views
Pie chart with sub-slices per category in Google Sheets?
I have a sheet that contains a list of books, with metadata about those books. Two columns that exist are the library within which the book is stored, and the condition of the book. I'd like to create ...
- 33
3
votes
2
answers
332
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 ...
- 433
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
107
views
How do I check a set of numbers in different columns against numbers in different columns
I am trying to make a Lotto checklist. I have my own numbers in 6 columns A2:F2, and I have typed the winning numbers in H2:M2, 6 columns also. Now I want to check if my numbers in A2:F2 have any one ...
- 57
3
votes
2
answers
238
views
How to select range into a cell based on drop down selection
I have two sheets in my Google spreadsheet: Meal Plan and Recipes.
Meal Plan looks like this:
Recipes looks like this:
I would like to write a formula or function which allows me to select a meal ...
- 195
3
votes
2
answers
7k
views
Getting #DIV/0 error in Google Sheets for AVERAGE, SUM, COUNT, etc on column generated by ARRAYFORMULA
I am trying to get the average value of a column (column C "Profit Margin" in the screenshot) in Google Sheets. For some reason, I get a #DIV/0 error when trying to use any sort of range functions ...
- 53
3
votes
1
answer
5k
views
Google Finance Historical Data with Multiple Attributes?
=GOOGLEFINANCE("NSE:"&A2, "all",(TODAY()-($N$2)),TODAY(),"DAILY")
The above code returns OHLC data for specified days with the date column.
What I want are only high, low and close columns.
=...
3
votes
2
answers
188
views
How can I maintain leading zeros in a formula?
I have an array formula as follows:
=ARRAYFORMULA("NUMBER-"&SEQUENCE(33,16,1,1))
The array of numbers from SEQUENCE run from 1 to 528, but I want all the numbers to have matching ...
- 31
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
3
votes
1
answer
8k
views
How can I track the time it takes for someone to fill out a Google form?
I am running a study for my thesis, and would very much like to track the time it takes to complete a Google form to compare against how long it takes to fill.
3
votes
2
answers
3k
views
Query returning #VALUE error when the column has no values
The below query returns #VALUE error when the column H is completely empty:
=QUERY('Input Longo'!A8:S;"SELECT '000', A, B, C, D, E, ' ', F, ' ', ' ', ' ', G, ' ', ' ', ' ', '"&...
3
votes
1
answer
2k
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(...
- 133
3
votes
1
answer
109
views
COUNTIF with Multiplicities
I am currently creating a histogram on Google Sheets to count how many of x-product in our inventory we have priced at less than $10k, between $10k to $20k, $20k to $30k, etc.
The problem is that ...
- 31