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
40 votes
5 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 ...
user avatar
1 vote
5 answers
2k 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 ...
user avatar
  • 24.8k
3 votes
3 answers
29k 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 ...
user avatar
  • 2,787
2 votes
1 answer
337 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, ...
user avatar
  • 23
2 votes
2 answers
1k 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.
user avatar
9 votes
6 answers
27k 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 ...
user avatar
  • 1,843
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 ...
user avatar
  • 13
12 votes
3 answers
30k 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 ...
user avatar
  • 167
6 votes
2 answers
2k 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
12 votes
3 answers
57k 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 ...
user avatar
  • 277
34 votes
9 answers
223k 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 ...
user avatar
  • 501
7 votes
6 answers
33k 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....
user avatar
  • 187
2 votes
1 answer
2k 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 ...
user avatar
  • 21
5 votes
2 answers
5k 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 ...
user avatar
1 vote
1 answer
7k 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 ...
user avatar
1 vote
1 answer
4k 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=...
user avatar
  • 11
0 votes
2 answers
410 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....
user avatar
  • 35
136 votes
3 answers
125k 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?
user avatar
  • 1,477
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 ...
user avatar
10 votes
5 answers
44k 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
3 votes
1 answer
6k 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 ...
user avatar
  • 479
1 vote
2 answers
13k 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/...
user avatar
  • 479
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. ...
user avatar
  • 307
7 votes
2 answers
2k 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 / ...
user avatar
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 ...
user avatar
2 votes
1 answer
249 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 ...
user avatar
1 vote
1 answer
484 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 ...
user avatar
  • 35
1 vote
4 answers
11k 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 ...
user avatar
  • 986
4 votes
5 answers
8k 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-...
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 ...
user avatar
3 votes
1 answer
361 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 ...
user avatar
2 votes
1 answer
64 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 ...
user avatar
2 votes
2 answers
468 views

Google Sheets' Duration Input issue

We have been using Apple Numbers which seems to have this duration thing figured out a little better than Google, (either that or I'm missing something). In Apple Numbers, if I type into a cell with a ...
user avatar
2 votes
1 answer
1k 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....
user avatar
  • 23
2 votes
1 answer
71 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 ...
user avatar
  • 57
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 ...
user avatar
1 vote
1 answer
45 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)=...
user avatar
  • 13
1 vote
4 answers
12k views

How Can I Sort a Data Range in Google Sheets Based on an Data Range?

I have been looking for a way to sort this information. I need to sort these items in this order: BK, BB, BV, BIV, BIII, BII, BR. I was trying to make a Google Script to get it, but I could only ...
user avatar
  • 25
1 vote
1 answer
140 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 ...
user avatar
  • 123
1 vote
1 answer
62 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=...
user avatar
  • 11
1 vote
2 answers
418 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 | ...
user avatar
  • 153
1 vote
1 answer
316 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 ...
user avatar
  • 25
1 vote
2 answers
121 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 * ...
user avatar
  • 147
1 vote
1 answer
71 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 ...
user avatar
  • 13
1 vote
1 answer
44 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 | --------------...
user avatar
  • 13
1 vote
1 answer
72 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 = ...
user avatar
  • 57
1 vote
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 ...
user avatar
1 vote
1 answer
78 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 ...
user avatar
  • 137
0 votes
1 answer
32 views

Sorting multiple columns using formula

Give a sequence of columns, I want to sort each column individually using formula because of some reason. Eg. A B C D 4 7 2 5 1 8 1 3 9 3 4 7 4 Expected output A B C D 1 7 1 3 4 7 2 4 8 ...
user avatar
  • 3
0 votes
0 answers
2k views

How can I select multiple values from a dropdown list in Google Sheets?

I have created a drop-down list, and I want to select multiple values every time I click on it. Is there a way to do without the script editor? https://docs.google.com/spreadsheets/d/...
user avatar