Skip to main content

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
43 votes
6 answers
37k 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 ...
Jacob Jan's user avatar
  • 23k
38 votes
9 answers
270k 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 ...
stim's user avatar
  • 541
14 votes
3 answers
71k 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 ...
RLH's user avatar
  • 297
14 votes
3 answers
42k 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 ...
AnneB's user avatar
  • 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 ...
Spongebob Squarepants's user avatar
11 votes
2 answers
14k 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, ...
Linda Linsefors's user avatar
10 votes
5 answers
46k 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?
user avatar
10 votes
6 answers
31k 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 ...
Dom Delimar's user avatar
  • 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. ...
v6ak's user avatar
  • 229
7 votes
7 answers
41k 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....
emihir0's user avatar
  • 187
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 / ...
Quells T's user avatar
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 ...
Melly Lilly's user avatar
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. ...
Bardo's user avatar
  • 317
6 votes
3 answers
32k 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] ...
Elad Ratson's user avatar
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 ...
user avatar
6 votes
2 answers
7k 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 ...
Travis Manning's user avatar
5 votes
4 answers
7k views

How to use max() with arrayformula()

This is my sheet : in column E, i want to find the max among col A,B,C using this simple formula in E1 : =max(A1:C1) But rather than pasting the formula to E2 .. E10, i want to see if possible to ...
andio's user avatar
  • 397
5 votes
1 answer
2k 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 ...
iamyourfriend's user avatar
5 votes
3 answers
1k 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: ...
Constantin Schreiber's user avatar
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 &...
Qwertiy's user avatar
  • 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 ...
Slav's user avatar
  • 73
4 votes
2 answers
28k 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 ...
kirsty's user avatar
  • 41
4 votes
2 answers
14k 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 ...
Pops's user avatar
  • 1,242
4 votes
2 answers
1k views

Is there a way to return multiple columns with XLOOKUP within an ARRAYFORMULA?

I'm using XLOOKUP to return a row with 4 columns. At the moment I have to copy/paste the formula every time new rows are inserted, so I thought of using ARRAYFORMULA to skip this step. The problem is ...
DSantiagoBC's user avatar
4 votes
2 answers
11k 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 ...
Adam Matan's user avatar
  • 1,803
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 ...
skytreader's user avatar
4 votes
1 answer
241 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, ...
JDD's user avatar
  • 65
4 votes
2 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 ...
Abbe Satty's user avatar
4 votes
5 answers
29k 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 ...
TonyF's user avatar
  • 41
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 ...
Melly Lilly's user avatar
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)),...
David's user avatar
  • 143
4 votes
1 answer
3k 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 ...
Torcrayth's user avatar
4 votes
1 answer
9k 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.
FrankieJLyons's user avatar
4 votes
4 answers
1k 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 ...
Steven D.'s user avatar
4 votes
1 answer
3k 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, ...
Vijetha Vardhan R N's user avatar
4 votes
5 answers
13k 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-...
Adele halsall's user avatar
3 votes
1 answer
8k 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 ...
Riccardo's user avatar
  • 589
3 votes
5 answers
3k 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 ...
user0's user avatar
  • 26.7k
3 votes
2 answers
313 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 ...
Chris Morris's user avatar
3 votes
3 answers
34k 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 ...
Mr. B's user avatar
  • 2,867
3 votes
1 answer
657 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 ...
FloatingRock's user avatar
3 votes
1 answer
19k 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 ...
Tomas's user avatar
  • 33
3 votes
2 answers
335 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 ...
jamesmstone's user avatar
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....
Kevin R's user avatar
  • 33
3 votes
1 answer
112 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 ...
Jun Palmejar's user avatar
3 votes
2 answers
409 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 ...
Matt W's user avatar
  • 205
3 votes
2 answers
8k 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 ...
Jesse T.'s user avatar
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. =...
Bhavesh Pamecha's user avatar
3 votes
1 answer
80 views

Google sheets and semi-autopopulation of data for attendance of a private school where students come two days a week and get to choose the day

The students in my school are not all scheduled to attend every day. In Google Sheets I have a sheet, 'student list' that lists the students that attend classes for each given weekday: The first row ...
Steven's user avatar
  • 33
3 votes
2 answers
966 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 ...
Chris's user avatar
  • 31

1
2 3 4 5
17