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
0 votes
1 answer
26 views

Sum multiple currencies by looking up conversion rates based on multiple cell values

I'm trying to sum values that are in different currencies. The currency for each value is specified in the previous column and the final result should be in Australian dollars AUD. I use a fixed ...
Phil's user avatar
  • 3
0 votes
2 answers
27 views

How for each cell in a row return and concatenate multiple matches without dragging down formulas?

I have a list of items and in another place I have list that covers some of these items each time with a different key. How to fill in column B all matching results next to the original item (column A)...
LWC's user avatar
  • 1,230
1 vote
2 answers
35 views

I'm looking for a way to return multiple values in an array formula on Google Sheets - haven't been able to find a solution

Scenario: I have a roster of dog owners who have had dogs involved in various training classes. I have a list of the dogs and their respective classes, and I have a list of the owners. I want to ...
Alison's user avatar
  • 11
0 votes
1 answer
36 views

How to use Array with Sumproduct or other formula

I am trying to use an array formula like SUMPRODUCT to perform a lookup with 2 criteria. I have a sheet named data that stores customer-specific pricing for products. A B C             D 1 ...
Anakin Teow's user avatar
0 votes
0 answers
10 views

MORE DETAILED explanation of ERROR: Array result was not expanded because it would overwrite data in B6 [duplicate]

Here are screenshots from my spreadsheet that has the issues I discussed in the previous question: IMPORTRANGE(,) works in a single cell, however, when adding the same formula to the next cell in the ...
Newton's cat's user avatar
-2 votes
1 answer
159 views

Solving ERROR: Array result was not expanded because it would overwrite data in B6 when using IMPORTRANGE

I am trying to make a Google Sheets book to keep track of my schedule. When importing data across sheets, I used IMPORTRANGE(url, range) to import data from Sheet1 to Sheet3. This worked at first; ...
Newton's cat's user avatar
0 votes
1 answer
29 views

Multiple substitutions in a table

Suppose we have a table like the following : A B C D E F G 1 17-01 DO 22-06 DO 17-01 22-06 22-06 2 22-06 22-06 17-01 DO 22-06 09-17 09-17 3 09-17 DO 22-06 22-06 DO 17-01 17-01 4 17-01 22-06 17-01 ...
Maidenas's user avatar
0 votes
1 answer
27 views

Cumulative SUM formula

I want a cumulative formula that will return the running total for each value in column B. For example, in the rows in column A: =SUM(B$1:B1) =SUM(B$1:B2) =SUM(B$1:B3) =SUM(B$1:B4) etc. I would like ...
Giovanni Capuano Solution's user avatar
0 votes
3 answers
29 views

Finding strings from a list between 2 string conditions that are part of the list

In Google Sheets, I have a list of unique strings that I want to filter based on two conditions. These conditions are two strings within that list. Ultimately, I want to find all the strings, ...
pfnanc's user avatar
  • 43
1 vote
1 answer
14 views

Sorting a range of data based on a column within that data

I have a set of data spanning columns A through P in Google Sheets. I would like to add a column in Q that will return the values of A based on the top values of column P. I assume this can be with ...
Colin Davis's user avatar
1 vote
2 answers
32 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
  • 43
0 votes
2 answers
22 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
24 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
67 views

How to concatenate all further columns from duplicated items of a first column?

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,230
0 votes
1 answer
30 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
20 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
48 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
  • 43
0 votes
1 answer
35 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
  • 43
1 vote
0 answers
39 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
80 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
239 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
239 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
200 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
1k 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
45 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
76 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
173 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
266 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
82 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,518
1 vote
2 answers
45 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
144 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
60 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
976 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
98 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
302 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
53 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
109 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
131 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
41 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
47 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
  • 43
-1 votes
1 answer
105 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
160 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
58 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
641 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
415 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,230
0 votes
1 answer
138 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
232 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
  • 237
1 vote
1 answer
1k 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
699 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
345 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

1
2 3 4 5
17