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
0 votes
0 answers
3 views

How to Parse each cells of column full of JSON?

I have a dataset where 2 out of 14 columns are in Json format. I know how to parse a JSON cell (one by one) with ImportJson(endpoint, path) but i would like to iterate for each row of these 2 columns
Boris Fredoc's user avatar
0 votes
1 answer
29 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 ...
Ian Y.'s user avatar
  • 113
1 vote
1 answer
130 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 ...
Varun's user avatar
  • 11
2 votes
2 answers
94 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 ...
Jaz's user avatar
  • 21
0 votes
1 answer
42 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 ...
orschiro's user avatar
  • 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 ...
BMCF's user avatar
  • 13
1 vote
1 answer
17 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 ...
JMad's user avatar
  • 11
0 votes
1 answer
29 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 ...
Catie Mackey's user avatar
3 votes
2 answers
189 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 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 ...
Jakov Grams's user avatar
2 votes
1 answer
182 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 £...
Richard Parnaby-King's user avatar
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 ...
Low Rep Mods are bad's user avatar
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 ...
Alex's user avatar
  • 11
0 votes
1 answer
45 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 ...
Pascale's user avatar
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(...
Adrik Ivanov's user avatar
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/...
Elaine's user avatar
  • 19
0 votes
1 answer
83 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 ...
Mrquestion's user avatar
-1 votes
1 answer
79 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 ...
Bharel's user avatar
  • 101
2 votes
2 answers
146 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 ...
manc's user avatar
  • 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 ...
Anto C's user avatar
  • 13
1 vote
1 answer
388 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 ...
jxh's user avatar
  • 13
1 vote
1 answer
254 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 ...
LWC's user avatar
  • 1,216
0 votes
1 answer
92 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 ...
Dustin's user avatar
  • 15
2 votes
1 answer
163 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 ...
Catalyx's user avatar
  • 227
1 vote
1 answer
550 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 ...
Ryan Voigt's user avatar
0 votes
1 answer
299 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. ...
bonko99's user avatar
0 votes
2 answers
152 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),"",...
Dustin's user avatar
  • 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: ...
Nels's user avatar
  • 1
0 votes
1 answer
533 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 ...
Axel Frey Guarenas's user avatar
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 ...
Axel Frey Guarenas's user avatar
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. ...
Axel Frey Guarenas's user avatar
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 ...
Jun Palmejar's user avatar
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 ...
Makayla Maxwell's user avatar
0 votes
2 answers
502 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, ...
Matan's user avatar
  • 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 ...
Elaine's user avatar
  • 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 ...
Febriana Angelina Suhendra's user avatar
0 votes
2 answers
284 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 ...
Matt's user avatar
  • 1
0 votes
1 answer
397 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 ...
Matthew W's user avatar
0 votes
1 answer
133 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 ...
Charononon's user avatar
1 vote
2 answers
216 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 ...
Meir's user avatar
  • 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 ...
XCATHADOR's user avatar
1 vote
1 answer
150 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 ...
MarioJBlidge's user avatar
1 vote
1 answer
38 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 ...
Alawar Walherich's user avatar
0 votes
1 answer
551 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 ...
Pierre's user avatar
  • 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,...
user305823's user avatar
0 votes
1 answer
45 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 ...
Spedwards's user avatar
  • 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)
Colin Mills's user avatar
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....
Nick Res's user avatar
  • 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 ...
Colin Mills's user avatar
0 votes
1 answer
130 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'...
Gary K's user avatar
  • 5

1
2 3 4 5
17