The Stack Overflow podcast is back! Listen to an interview with our new CEO.

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
53
votes
3answers
41k 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?
35
votes
3answers
25k 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 ...
17
votes
9answers
97k 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 ...
9
votes
5answers
36k 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?
9
votes
3answers
12k 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 ...
7
votes
5answers
19k 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
3k 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
14k 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 ...
5
votes
2answers
685 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
875 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
7k 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
7k 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
93 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
1answer
5k 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. ...
4
votes
2answers
739 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
2k 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
4k 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
116 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
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 ...
2
votes
2answers
6k 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
36k 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
1answer
8k 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 ...
2
votes
1answer
852 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
147 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
387 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
228 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
1answer
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
8k 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
25 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
23 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
108 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
258 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
1answer
67 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
29 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 ...
2
votes
1answer
19 views

ArrayFormula to create a table of data from 1 column

I have the below formula and want to turn it into an array. =INDEX($A:$A,ROW(A1)*5-5+COLUMN(A1)) I have tried it with the below attempt but only get 1 single cell result. =arrayformula(INDEX($A:$A,...
2
votes
1answer
37 views

How to aggregate data from multiple Google sheets with a tag

I am importing data (using IMPORTRANGE) from 3 different sheets to 3 different tabs of a google sheet. Then I am aggregating those three sheets into one and also filter them using a query. Ex: =...
2
votes
1answer
28 views

Boolean test of whether a time is before or after midnight in Google Sheets?

I'm having a job trying to create a Google sheet which calculates the difference between the time I fell asleep and the time I woke up. Here's what I want to happen: Sleep time Wake time ...
2
votes
2answers
65 views

Processing data from multiple cells and comparing results with another cell

For most orders, I have multiple deliveries under the same recipient ID and I need to compare whether everything ordered is really delivered. All products are divided by a comma and have always the ...
2
votes
2answers
306 views

ARRAYFORMULA with IF, DETECTLANGUAGE and LEN

I'm trying to make an array formula to detect the number of English characters in a range. When I use the following for individual cells: =IF(DETECTLANGUAGE(ARRAYFORMULA(A5:A))="en",LEN(ARRAYFORMULA(...
2
votes
1answer
197 views

Remove the false operator in Google Sheets

Here I am trying to add a random hexadecimal value every time a name is entered. It’s working but the other empty cells get filled with false. I want to completely delete the false part as it’s ...
2
votes
1answer
658 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 ...
2
votes
1answer
354 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 ...
2
votes
1answer
16 views

Dynamically creating column names based on data in another tab

I have a Google sheet that logs all submissions on my website of various contact forms. I need to know how many times each contact form has been submitted, and in which week they were submitted. The ...
2
votes
1answer
27 views

Google Sheets sort question

I used to know this a few years ago and for the life of me can't figure it out and can't find an answer in Google help. I know how to sort columns, but only by the default A-Z format, how can I sort ...
2
votes
1answer
35 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 ...
2
votes
1answer
26 views

How to SUMIFS text beginning with apostrophe

I am receiving a list of products from an external data source. One of the product names begins with an apostrophe. When I attempt to produce a summary of the products and total quantity using UNIQUE()...
2
votes
2answers
19 views

How do I automatically enter sequential numbers without using the mouse in Google Sheets?

I would like to create a series of number in Google Sheets, without dragging with the mouse. When using the Shortcut Cmd+R (Fill Right) it just copies the value of the first cell. Initial State: ...