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
74
votes
3answers
61k 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?
36
votes
4answers
28k 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
votes
9answers
124k 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 ...
11
votes
4answers
2k 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 ...
10
votes
3answers
16k 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 ...
9
votes
5answers
40k 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?
7
votes
5answers
21k 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 ...
7
votes
1answer
5k 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, ...
6
votes
2answers
18k 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 ...
6
votes
1answer
8k 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. ...
6
votes
2answers
1k 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 ...
5
votes
2answers
1k 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 / ...
5
votes
2answers
9k 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. ...
5
votes
2answers
8k 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 ...
4
votes
1answer
20k 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 ...
4
votes
3answers
11k 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] ...
4
votes
1answer
9k 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 ...
4
votes
4answers
193 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 ...
4
votes
2answers
945 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 &...
4
votes
4answers
3k 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
2answers
7k 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 ...
3
votes
1answer
65 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 ...
3
votes
2answers
190 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 ...
3
votes
2answers
864 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
1answer
18 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, ...
3
votes
1answer
58 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 ...
3
votes
1answer
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
4answers
10k 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....
2
votes
2answers
9k 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 ...
2
votes
2answers
39k views

Google Spreadsheet formula, MINUS?

I have a spreadsheet with 3 columns: | ITEM | COST | SUBTOTAL | Subtotal, for example, is 1000 to start, like: -------------------------- | ITEM | COST | SUBTOTAL | -------------------------- | ...
2
votes
3answers
13k 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
votes
1answer
1k views

How to block automatic row and column insertion in Google Sheets?

While playing around with array formulas, "sometimes" when the result has more rows and columns than the previously available in the sheet, Google Sheets automatically adds the row needed. I said "...
2
votes
1answer
158 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....
2
votes
3answers
93 views

Find and extract words/names from a string beginning with specific character

I've got some rows in Google Sheets that contain strings. I want to extract certain parts from this string and enter them into another cell: specifically, I want to extract words/names that begin with ...
2
votes
1answer
169 views

How can I use a formula to split a text into individual characters, regardless of length?

I have a list of words in Japanese. I want to write a formula that will input the word, e.g. ひらがな, and output it as an array of however many characters: ひ + ら + が + な, no matter the length of the word....
2
votes
1answer
520 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 / ...
2
votes
1answer
265 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, ...
2
votes
2answers
1k views

Appending text to each column in Google Sheets

I have a Google Sheets that has about 200 columns. Each one is a name, and to each one, I want to append -banner.jpg. For example: abcde fghij klmno will all be updated to: abcde-banner.jpg fghij-...
2
votes
2answers
2k views

How can I fill the diagonal of a matrix with zeros?

I have an empty matrix: I would like to fill the diagonal of a matrix with zeros: Is there any clever way to do it? The matrix can be pretty large and it's tedious to do this manually. I could use ...
2
votes
5answers
11k 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 ...
2
votes
1answer
28 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 ...
2
votes
1answer
49 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 ...
2
votes
1answer
70 views

Using dates as criterion for COUNTIFS in Google Sheets

So I'm currently making a recruiting data sheet for an online community. I'm trying to make it as automated as possible without actually making a bot to do it. How can I make a COUNTIF(or similar) ...
2
votes
1answer
279 views

How to extract multiple paths from a single string of URLs in Google Sheets?

How can I get a subrange of second to last element in a formula under Google Sheets? I'm trying to extract all occurrences of https://example.com/* in a cell which may contain multiple URLs scattered ...
2
votes
1answer
256 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 ...
2
votes
1answer
114 views

How to sum the values of same vlookup results?

I have this formula it gets the single data from another file from a sheet at column 7. =ARRAYFORMULA( if( len($A2:$A); iferror( vlookup( ...
2
votes
2answers
334 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
2answers
603 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, ' ', ' ', ' ', '"&...
2
votes
2answers
986 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 ...
2
votes
1answer
102 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?

1
2 3 4 5
11