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
1 vote
2 answers
16 views

Finding preceding n results from a list based on conditions in Google Sheets

I have the following list in a Google Sheet: List Apple Banana Pear Grapes Kiwi Mandarin Papaya Orange Tomato Plum Watermelon Based on these two following conditions, I'm trying to get ...
pfnanc's user avatar
  • 41
0 votes
2 answers
20 views

I wanted to set the maximum value of each unique input as active

In the example data below, for each unique SYS Name, the Status column is marked Active in the row with that SYS Name's highest Serial Number. In Status, Column C, for each unique Sys Name the row ...
Vintikon Indonesia Perkasa's user avatar
1 vote
1 answer
20 views

Formula to return a value when the adjacent column's text contains any value from an array of text strings

In Google Sheets I have an array named "Tech_issues" with the following values: Array name value 1 value 2 value 3 value 4 Tech_issues crash froze glitch error I'm looking to create a ...
flipskyrain's user avatar
2 votes
1 answer
60 views

Can an Google Sheet Filter function base its include on a whole row?

In Google Sheets I have two or more columns of data: Column 1 is a list of items that may include duplicates. Column 2 (and any additional columns), contains keys each associated with the item from ...
LWC's user avatar
  • 1,228
0 votes
1 answer
23 views

Counta and Array_constrain formula in Google Spreadsheets

I am creating a sheet in google sheets to look like a dashboard from the below data. I am asked to create this with formulas. I am getting the filter part correct. But changing the values from filter ...
Ramya's user avatar
  • 1
1 vote
1 answer
19 views

Hide a value in a cell unless another cell has a given value

I'm trying to create a formula to generate a list of values in a dropdown. Each row contains a task that has a status, and a priority. The priority is set from a dropdown containing a list of numbers ...
Christian Guest's user avatar
0 votes
2 answers
41 views

Googlel Sheets formula to SUMIFS with multiple AND and OR criteria

I have a dataset similar to the image below. I am trying to calculate the sum in column A if: Region (G) = NorAm AND Team (H) = Corporate AND Stage 7 (F) <= 2/1/24 where either of these conditions ...
Elaine's user avatar
  • 33
0 votes
1 answer
31 views

Populate cell based on unique values from two fields

I have a data set similar to the google sheet image below. I currently have a formula in column C that populates the first unique value in column B. ARRAYFORMULA(INDEX(IF(B2:B="""",...
Elaine's user avatar
  • 33
1 vote
0 answers
33 views

Google Sheets Arrayformula for timestamps of checkboxes changed

I am looking for LAMBDA-based formula for hundreds of rows which will add timestamp based on checkbox changed for that particular row. Need it to be ARRAYFORMULA/BYROW, as number of rows grows ...
wass rubleff's user avatar
3 votes
1 answer
78 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
1 vote
1 answer
112 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
2 votes
1 answer
190 views

LAMBDA or ARRAYFORMULA with GOOGLEFINANCE

Working on a formula in Google Sheets to return True/False based on searching through an array of data using: =TO_DATE(INDEX(SORT( GOOGLEFINANCE( A2,"high",TODAY()-365, ...
RTG's user avatar
  • 23
0 votes
1 answer
148 views

Google Sheet combine rows with same date

I'm loosing my mind with problem in Google Sheets. We are using Appsheet to insert data for daily revenue for different services. So rows are days and columns are services with revenue. I have several ...
Karnak's user avatar
  • 103
4 votes
2 answers
917 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
0 votes
0 answers
38 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 ...
Boris Fredoc's user avatar
0 votes
1 answer
70 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
161 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
186 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
73 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,488
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
58 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
50 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
632 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
91 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
278 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
50 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 ...
Web App User's user avatar
1 vote
1 answer
80 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
118 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
34 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
46 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
  • 33
0 votes
1 answer
182 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
102 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
158 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
57 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
564 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
373 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,228
0 votes
1 answer
134 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
210 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
949 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
558 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
259 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
73 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
1 vote
1 answer
1k 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
19 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
137 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
111 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
30 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
740 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
42 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
  • 33
0 votes
2 answers
336 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

1
2 3 4 5
17