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
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
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
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.
Comunicação Andreia's user avatar
2 votes
1 answer
378 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, ...
Clay's user avatar
  • 23
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
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
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
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
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 ...
DwightM's user avatar
  • 13
1 vote
1 answer
55 views

Joining multiple sheets horizontally

I have a number of spreadsheets I want to join them horizontally by Id. How would you achieve that? p.s I should mention that the id order could be different in each spreadsheet
user2926340's user avatar
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
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
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 ...
Mike's user avatar
  • 31
1 vote
1 answer
5k 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=...
Dave's user avatar
  • 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 ...
Marek Marhoul's user avatar
0 votes
2 answers
820 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....
Clayton's user avatar
  • 45
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
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
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
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
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 ...
Damien B'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
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
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
2 votes
1 answer
505 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 ...
TheDT's user avatar
  • 45
2 votes
1 answer
70 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 ...
littlecoder's user avatar
2 votes
1 answer
110 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 ...
Kedar's user avatar
  • 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 ...
que syrah sarah's user avatar
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 ...
Vignesh Rams's user avatar
2 votes
1 answer
369 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 ...
Kyle Chapman's user avatar
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 ...
PY_'s user avatar
  • 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 ...
Abiel Muren's user avatar
1 vote
2 answers
203 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 * ...
fsteff's user avatar
  • 147
1 vote
1 answer
230 views

Stop Google Sheets from removing trailing zeros

Can anyone suggest a Google Sheets formula to stop removing trailing zeros? (but allow truncating to 6 digits after decimal? 00.000000 29.9918650 -93.958005 29.991865 -93.958005 (1mi:29.991865:-...
Most Interesting Man In World's user avatar
1 vote
1 answer
52 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 | --------------...
Vincent's user avatar
  • 13
1 vote
2 answers
452 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 | ...
Tom's user avatar
  • 163
1 vote
1 answer
87 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)=...
GM B's user avatar
  • 13
1 vote
1 answer
357 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 ...
nalka's user avatar
  • 143
1 vote
1 answer
196 views

Google Sheets, using VLOOKUP and ARRAYFORMULA to search multi-line cells

I'm trying to use VLOOKUP to find the batch number of a product based on its serial number. The problem is that the serial numbers for each batch are stored in one cell per batch, separated by ...
DakotaE's user avatar
  • 23
1 vote
1 answer
200 views

How to use a query to find a row of same name but last entry, then copy a value next to it (update request)

(This is an updated version of this question - I am trying my best to make myself clearer this time.) I am trying to use a query to copy values from one column to another. Both source and destination ...
sofname's user avatar
  • 137
1 vote
1 answer
69 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=...
Ritz's user avatar
  • 11
1 vote
1 answer
163 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 ...
Delgado's user avatar
  • 13
1 vote
1 answer
82 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 = ...
Kedar's user avatar
  • 57
1 vote
4 answers
16k views

Numeric before query but query results are not a numbers

I have a column of values, let's say column A, that if I do =isnumber on any of the column data it returns TRUE. Now after I =query and select A, the query gives me the right values, but the type ...
jason's user avatar
  • 1,054
1 vote
1 answer
798 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 ...
Xan Mei's user avatar
  • 35
1 vote
2 answers
15k views

Google Sheets Arrayformula on both horizontal & vertical cells?

In a sheet, I make calculations horizontally, however I can't build the arrayformula to expand calculation vertically on each row. See the following sheet: https://docs.google.com/spreadsheets/d/...
Riccardo's user avatar
  • 589