Questions tagged [google-sheets-arrayformula]
Use for questions about the use of the Google Sheets built-in function ARRAYFORMULA.
838
questions
1
vote
1
answer
25
views
How can I get VLOOKUP to return a response that is in one of two adjacent columns?
I have the following working formula in my header row and that returns an array of "Project Team Owner" values from my Form Responses sheet based on a matching Unique ID:
={"Project ...
- 29
0
votes
1
answer
26
views
Arrayformula when using index match
I have the following index match formula in this sheet.
Project Tracking Tab, column T
=IFERROR(INDEX('Staff Effort'!A8:AW,
MATCH(A2, 'Staff Effort'!A8:A, 0),
MATCH('Staff Effort'!AW8, 'Staff ...
- 29
0
votes
1
answer
25
views
Can you do an arrayformula when using index match?
I have the following index match formula in this sheet.
Project Tracking Tab, column B
=IFERROR(INDEX('Project List'!A4:B,MATCH(A2,'Project List'!A4:A,0),MATCH('Project List'!B4,'Project List'!A4:B4,0)...
- 29
0
votes
1
answer
22
views
Help converting counta to an array formula
I need help adding turning my formula into an Array Formula
I am using the COUNTA to count all cells in row range that are not empty ignoring blank cells with formulas. It works, but will not copy ...
- 29
0
votes
1
answer
19
views
My unique ID is not copying down when using arrayformula
Hi I am using the following formula to create a unique row id using values in other cells in my Google Sheet. When I add arrayformula it does not copy down. What am I doing wrong?
Works
=B2&"...
- 29
0
votes
1
answer
20
views
Google Sheets Conditional Formatting w/ Several Criteria
I posted a similar question here yesterday, but didn't realize that I should have included more info/conditions.
Basically I am starting with a spreadsheet of timesheet data. Each row is a single ...
- 13
1
vote
1
answer
24
views
Google Sheets Conditional Formatting w/ Multiple Criteria
I'm trying to format a spreadsheet containing timesheet data to flag certain conditions.
Each row of data represents a single pair of clock-in and clock-out times for an employee.
There can be several ...
- 13
0
votes
1
answer
23
views
How to pull all the data on the "right" of a column with certain value in the header
I am trying to pull out certain columns of information from a autogenerated report for further analysis. I will need to only pull certain columns based on the header values.
Here is the link to the ...
- 29
0
votes
1
answer
24
views
How can I manually edit empty cells that are within an array returned by an ArrayFormula function?
We have a Google Form whose responses are stored in Google Sheets, and that data is used by formulas in other sheets.
For example, one sheet uses the following formula to return form response data ...
- 1
0
votes
0
answers
16
views
Generate unique project ID based on certain criteria
I am working on my company's sales database to create an unique ID for each sale (or project) base on the customer ID, product ID and sale date as the table below. These criteria are necessary to make ...
1
vote
1
answer
27
views
How to isolate the earliest and latest times and eliminate any times in between on base on a date
I'm trying to figure out how to isolate only the first and last times from a specific day in Sheet1 A:A and determine the exact hours between the first "Load Out" and the final "Load In&...
- 11
0
votes
1
answer
65
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 ...
- 3
0
votes
2
answers
42
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)...
- 1,250
1
vote
2
answers
209
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 ...
- 11
0
votes
1
answer
46
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
...
- 13
0
votes
0
answers
18
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 ...
-2
votes
1
answer
914
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; ...
0
votes
1
answer
31
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
...
- 23
0
votes
1
answer
80
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 ...
0
votes
3
answers
42
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, ...
- 43
1
vote
1
answer
16
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 ...
- 11
1
vote
2
answers
34
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 ...
- 43
0
votes
2
answers
23
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 ...
1
vote
1
answer
49
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 ...
- 11
2
votes
1
answer
74
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 ...
- 1,250
0
votes
1
answer
50
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 ...
- 29
1
vote
1
answer
25
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 ...
0
votes
2
answers
63
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 ...
- 43
0
votes
1
answer
42
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="""",...
- 43
1
vote
0
answers
55
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 ...
- 171
3
votes
1
answer
83
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 ...
- 33
1
vote
1
answer
515
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:-...
2
votes
1
answer
295
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,
...
- 33
0
votes
1
answer
260
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 ...
- 103
4
votes
2
answers
2k
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 ...
- 143
0
votes
0
answers
53
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 ...
0
votes
1
answer
92
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
186
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
395
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
89
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,590
1
vote
2
answers
48
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
268
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
75
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 ...
4
votes
2
answers
1k
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 ...
- 41
1
vote
1
answer
109
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
348
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
55
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 ...
- 581
1
vote
1
answer
151
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
143
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
72
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