Questions tagged [google-sheets-arrayformula]
Use for questions about the use of the Google Sheets built-in function ARRAYFORMULA.
806
questions
1
vote
0
answers
18
views
How to parse multiple cells in one go using ImportJSON()?
I have a dataset with 14 columns. Two of the columns contain text that is in JSON format.
I am currently parsing the data with ImportJSON(), a script that is a collection of custom functions. It has ...
- 11
0
votes
1
answer
34
views
How to make arrayformula automatically applied to newly inserted rows?
The following arrayformula needs to be applied to the entire column B, so that it populates Twitter account IDs in the cells of Column B according to hyperlinks in Column A. It firstly checks whether ...
- 113
1
vote
1
answer
131
views
Multiple large GOOGLEFINANCE formulas make spreadsheet unresponsive
Problem
In Google Sheets I have huge GOOGLEFINANCE formulas that return accurate results however the sheet is often unresponsive, and can take a few hours to recalculate when reloaded.
Is there a way ...
- 11
2
votes
2
answers
100
views
How to combine ARRAYFORUMLA with INDIRECT to iterate over multiple sheets?
In Google Sheets I have employees' expense data.
Each sheet contains data for multiple employees, for a single month.
In a main sheet I have a formula that returns the SUM of all expenses for a single ...
- 21
0
votes
1
answer
44
views
How to shuffle a range but prevent refresh?
I would like to achieve the following:
Extract a specified range based on user input in a cell using the arrayformula
Shuffle/randomise that extracted range
I have tried several approaches such as a ...
- 5,468
1
vote
2
answers
44
views
Payroll table creation challenge - Fill rows with various text requirements and repetition
In the "Payroll export" worksheet, I need to take the data in columns C through AL, and convert it to what I've built columns AN through AR.
I have used an Array formula to paste the ...
- 13
1
vote
1
answer
18
views
Google Sheets formula to round up columns to nearest thousand and sum
So i have a column with numbers and want to sum the column, but i want them to be rounded up to the nearest thousand.
=SUM(ARRAYFORMULA(ROUNDUP(J4:J ,-3)))
I tried this however I am just getting a ...
- 11
0
votes
1
answer
32
views
How do I use an array formula to get column data from a sheet based a checkbox?
I have 3 sheets.
The first one Master contains my source data.
In the other two sheets I'm trying to get columns A-C from the source data Master!A:C based on the state of the checkboxes in columns D ...
3
votes
2
answers
200
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 ...
- 31
1
vote
1
answer
69
views
Unable to create an ARRAYFORMULA that will AVERAGE the results of a QUERY
I have a sheet data that tracks client transactions.
Each row is a transaction
The columns are:
LAST NAME
PHONE NUMBER
SUB
TOTAL
TIP
PERCENTAGE
The TIP and PERCENTAGE columns are calculated based ...
- 11
2
votes
1
answer
184
views
How to apply ArrayFormula on a RegexReplace Query formula so that it autopopulates for all values in Column A?
I'm trying to create a single string for each product that includes different unit prices tied to purchase quantity. For example, a product might have a unit price of £10, but the unit price drops to £...
2
votes
1
answer
49
views
Sum of Cells (supplementary question)
This question which specifically referred to summing a column of numbers until a break and then subtotalling on the next following line was answered very helpfully by this answer.
Supplementary to ...
1
vote
1
answer
41
views
Iterating over two arrays and comparing each pair
Essentially, I would like to iterate over two 2-dimensional arrays of cells, and compare each pair of cells via REGEXMATCH.
I briefly attempted nesting ARRAYFORMULA, however this didn't seem to give ...
- 11
0
votes
1
answer
46
views
How do I return the value of a specific cell within a range on Google Sheets based on current date?
I have a spreadsheet that I'm using to track weekly performance metrics and want to create a summary page that only shows me the value of each metric for the current week.
The worksheet is currently ...
- 1
0
votes
1
answer
29
views
How do you add 2 leading zero to the first column and 1 leading zero to the second column in an array formula?
I have spreadsheet and I need a way to format the formula so that the first column have two leading zeros (000) and the second column to have one leading zero (00)
Minimum required code:
=ARRAYFORMULA(...
- 111
0
votes
1
answer
41
views
Formula to check is cells are blank. If the cell is not blank then assign a value
I have a data set on Google Sheets where for example,
Name
Stage 1 Date
Stage 2 Date
Stage 3 Date
Stage 4 Date
Bob
3/1/2023
3/5/2023
3/31/2023
4/15/2023
Joe
2/1/2023
Zoe
3/21/2023
4/1/2023
4/15/...
- 19
0
votes
1
answer
84
views
Is it possible to modify with a formula the same cell where you introduce the value in Google Sheets?
So imagine we have column A, B and C. And I want to sum A1 + B1
You can do type in C1 "=SUM(A1,B1)" and you will see the result in C1
But, is there any way we can introduce the formula in A1 ...
- 109
-1
votes
1
answer
82
views
How does ArrayFormula work?
I'm trying to understand when does ARRAYFORMULA decide to expand a range, and when does it decide to repeat it.
According to this StackOverflow question:
The rules for ARRAYFORMULA are:
If a ...
- 101
2
votes
2
answers
147
views
Sum of all numbers from many columns that match text from multiple rows in another column
I have some data from analytics that I need to combine somehow and trying to find the best way of doing this. I have data similar to the following:
URL
Sessions
/
59
/?variable=1
2
/?variable=2
37
...
- 135
1
vote
1
answer
56
views
Trouble with a formula in Sheets to forecast shipment arrival
I have big trouble making my formula works and I am pretty sure that this is simple.
So simple that I am litteraly struggling on it for weeks now.. So I give up :'(
Let me explain:
I am using sheets ...
- 13
1
vote
1
answer
400
views
ARRAYFORMULA and MAXIFS: does it work like SUMIFS?
I have an existing formula that is sort of a cumulative MAX of another column: it has a range covering only the rows above it, inclusive. So B2 is =MAX(A$1:A2), and I can run this down or auto-fill ...
- 13
1
vote
1
answer
261
views
How to do an arrayformula that transposes a list while multipling the entries? [closed]
I have a list of rows, and I like to transpose them into a list of columns.
But the trick is how do I repeat each of the new columns 4 times (1 row will become 4 columns)?
Alternatively, how do I add ...
- 1,216
0
votes
1
answer
94
views
Return Value to Different Line with Possible Duplicate Information
In Google Sheets, I need to extract values of D in specific rows and insert them into column I where appropriate.
See link below. Here is an explanation of what I have so far in the spreadsheet:
Raw ...
- 15
2
votes
1
answer
164
views
Append Current Stock Stock Price to Historical Prices
I'm looking to append the current share price of a stock to the end of a period with changing durations.
My current formula for the historical prices is (where cell B3 is a duration of days to ...
- 227
1
vote
1
answer
575
views
How do I build a formula to select a Category based on different select keywords/phrases in a determined column?
I'm hoping to build a formula to automatially select a category based on select keywords/phrases from an imported bank statement.
I attached an editable copy of the Google Sheet I'm looking for help ...
- 13
0
votes
1
answer
310
views
Google Sheets - Array Formula that searches each row and returns a cell value based on conditions
My data contains traffic source data from Google Analytics, grouped by customer. For each row, there is a TRANSPOSE(ARRAYFORMULA( function that fills a horizontal array of values from another sheet. ...
- 3
0
votes
2
answers
159
views
What's wrong with my formula?
In Google Sheets:
First, I'd like to return the values in the whole column of D into Column H if:
E and F equal A and B
C is not blank
This was the formula I tried:
=IF(ISBLANK(C2=TRUE),"",...
- 15
0
votes
1
answer
60
views
ArrayFormula to transfer items in a column from one google sheet to a column in another google sheet ignoring blank cells
I'm trying to transfer items in a column from one google sheet to a column in another google sheet ignoring blank cells entirely.
I have this formula that will transfer all cells including the blanks:
...
- 1
0
votes
1
answer
557
views
All columns values in one column
I need some help! :S
I have a spreadsheet and three list of items (Column "A", "B" and "C") and I need to put all the values of all the columns in only one column (Column ...
1
vote
1
answer
18
views
Import values from others spreadsheets to a single one
I'm having some trouble to import data values from many spreadsheets to a single one. I need to import all the values from all columns "A" of each spreadsheets to the column "A" of ...
0
votes
2
answers
74
views
Put duplicates values at zero
I'm having some trouble finding a formula to put(replace) my duplicates values into "0". I tried everything I know (not a lot) and nothing works. I have two columns: Column A and Column B. ...
3
votes
1
answer
107
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 ...
- 57
0
votes
1
answer
29
views
Check What Same Text Is Found In A List & List The Text
I have 2 columns of values. I want to create a formula that looks at Column A and finds what Column B text values are within in the Column A items. Then I want what matches to list in column C. I've ...
0
votes
2
answers
523
views
Google Sheets: Arrays: using ARRAYFORMULA() v. { }
Why does this work: =ARRAYFORMULA(LEFT(A2:A13,1)),
but this doesn't: ={LEFT(A2:A13,1)}
ie, using ARRAYFORMULA returns the array: J
F
M
A
M
J
J
A
S
O
N
D, while using the braces instead of the formula, ...
- 21
0
votes
1
answer
39
views
Formula to randomly select the name associated with lowest value from data set & a random from the lowest options if tied with drop-down select filter
I have a data set on Google Sheets where for example,
Name
Scores
Region
State
First
Row
Row
Row
Second
Row
Row
Row
On another tab, I have a drop-down list to select a specific Region and State. I ...
- 19
0
votes
0
answers
72
views
Array formula & concatenate
i want to make 6 number id code with array formula by combining the job code and the number list... 2 first number is the job code and 4 last number is the number list..
I tried to divide the job code ...
0
votes
2
answers
286
views
Can you do an importrange on data from multiple sheets if the data itself has an arrayformula with a textjoin?
I'm trying to summarize a bunch of sheets into one via a query([(importrange)(importrange)(etc.)}. I've done this multiple times without issue, but now the data I'm pulling is has a columns that is ...
- 1
0
votes
1
answer
409
views
Use MATCH & LARGE but with 2 identical highest values in Google Sheets
Thank you to doubleunary for quickly solving my first of two questions. I am now able to create a single cell that properly lists all of the top 5 largest value column titles separated by commas; even ...
0
votes
1
answer
140
views
Google Sheet >> Trying to count in sequential
I have a set of data, which the value might be repeated and I am attempting to count and mark the counting in sequence.
I am currently trying to create a list using arrayformula (which is a must for ...
1
vote
2
answers
221
views
Google sheets - what function can I use to expand MAXIFS function to an entire column?
I'm trying to make the current column (most recent interaction date with client) display the max values (most recent dates) from ContactLog!b:b (dates of all recorded interactions), when the client ...
- 23
1
vote
1
answer
39
views
simplify redundant information
Is there a way to have the formula eliminate the redundancy. Can i specify the set of unique cells in a grouping and have the redundant part simplify to one line and have it repeat to all the unique ...
- 99
1
vote
1
answer
155
views
merge data of columns that have the same label vertically in google sheets
I have a couple of datasets with the same columns entered horizontally next to each other.
I have a data / facebook leads / facebook adspend column for thee moonth of january in column a, b, c and the ...
- 35
1
vote
1
answer
39
views
Match taking into account characters like ä ë ï ö ü, á, é, í, ó, ú
Hey I am running against a small problem that I do not know the answer of.
I have 2 lists:
List 1 has all my data, for example
Märio
Toad
Bowser
Pëach
Luigi
List 2 has data too (but this list doesnt ...
0
votes
1
answer
558
views
ARRAYFORMULA - Return of Text if cell is not blank and DATE earlier than TODAY
After some unsuccessfull workings hours, I definitely need your support.
I am trying to create an ARRAYFORMULA, so that new rows can be added manually with the wished formulas already implemented in ...
- 1
0
votes
1
answer
21
views
Return Average of a column if
Looking to return average if criteria is met, but I can't get the formula to work when one of my criteria exists in multiple columns. Does anyone have an efficient solution?
=AVERAGEIFS(Sheet2!D:D,...
0
votes
1
answer
48
views
Keeping row content bound in Google Sheets
I have three main sheets in my spreadsheet. The first is a collection if items with relevant details for them, the second is a list of people. The third is used to set some basic relations between the ...
- 215
1
vote
1
answer
33
views
How to use ARRAYFORMULA to remove values that contain brackets?
I am trying to write a formula that pulls all data from column A that doesn't contain brackets.
Source Data
Desired Result
(no values containing brackets)
- 31
0
votes
1
answer
25
views
How to build a table of imported data from conditional data sources
I have many spreadsheets where data will be added manually and which are all identical. Let's call these UserInputSpreadsheets.
Inside each user spreadsheet are tabs with data which are also identical....
- 101
1
vote
1
answer
11
views
Remove 0g value from when pulling data from a column
Looking for help on writing a formula to pull all data from a column AC (picture 1), removing 0g values and move that data into a new column with no spaces which would look like picture 2.
Thanks in ...
- 31
0
votes
1
answer
132
views
TRANSFORM ROW WISE DATA INTO ONE COLUMN CONSECUTIVELY based on the number of values in each row
I've learned how to use filters to organize row-wise data into columns and am taking that to heart in this spreadsheet! But I've hit a major snag.
If you look at the example I've attached here, you'...
- 5