Questions tagged [google-sheets-arrayformula]
Use for questions about the use of the Google Sheets built-in function ARRAYFORMULA.
766
questions
1
vote
1
answer
26
views
Add a constant number to google sheets query result
how do i get this result? i want to get the number one in every row
- 11
1
vote
2
answers
27
views
In Google Sheets, can you repeatedly autofill until next non-blank cell all at once? (previous value)
Basically, what I want to do is fill all of the blank cell in a column with the value of the previous non-blank cells. For example:
I was wondering if there was a way to do this without having to do ...
- 11
0
votes
0
answers
19
views
How to retrieve today and tomorrow's values in a specific tab of a dynamic calendar
Employees are selected in their corresponding shift on each month tab in a dynamic calendar
See 092022 tab in my example:
I want Pemanence tab will be able to retrieve automatically the values in the ...
- 21
1
vote
1
answer
24
views
Combine different sheets with importrange for dynamic data from google forms
I have 4 identical google sheets that get continuously filled with data from 4 different google forms. I want to merge the 4 resulting sheets into one Final Sheet. Moreover I want to be able to ...
- 11
1
vote
1
answer
16
views
Google Sheets Remove Inconsistent Characters
I would like a formula that removes the ending of each of the text so it's consistent
https://docs.google.com/spreadsheets/d/1ysjz4B6Eh4eE5y_cYg76_CilkUena6iQDZtIIMhXSOs/edit?usp=sharing
- 87
1
vote
1
answer
35
views
Split, Flatten, and Repeat Cells in Certain Columns Within an Array Formula for Google Sheets
I am looking for a formula in google sheets to to break down an existing data set at once in a format that's a little more versatile. Here is the link to the sample sheet which is also the source of ...
- 11
3
votes
2
answers
51
views
How to select range into a cell based on drop down selection
I have two sheets in my Google spreadsheet: Meal Plan and Recipes.
Meal Plan looks like this:
Recipes looks like this:
I would like to write a formula or function which allows me to select a meal ...
- 175
1
vote
1
answer
34
views
Google Sheets - How can I return more than one match result from the same search key?
Link to sheet used: Testing Sheet
Thanks to another answer I was able to match and return a single word from a search_key being compared to a specific range using the following formula:
=UNIQUE(...
- 11
1
vote
1
answer
13
views
i'm not sure what function I want for this
I have a column that holds the month number(1....60) I want in another column to indicate what the number "1" is (in this case January 2023) I need a formula to look at a column, if the ...
1
vote
0
answers
10
views
Add more conditions to the formula
=query({IMPORTRANGE("1c6ihzJjzD_Nhbz5rB1s0485PS98V-Q4bfVAiIon5vSA","MC2!A11:S5000")},
"select Col19, Col1, Col2, Col3, Col5, Col6, Col7, Col8, Col9, Col10, Col11, Col12, ...
- 11
1
vote
1
answer
23
views
Use of COUNTIF in an array?
Overview
In Google Drive I have a sheet in which I frequently export my Spotify playlists. From this list I like to document artists I listen to.
In short:
Column A holds all the artists occurring in ...
- 23
2
votes
0
answers
26
views
How to count cells and blank cells after up until another cell in separate columns
The title is a bit confusing, but essentially I am creating a calendar/tracker where a user inputs values into the "Work" column and the spreadsheet should count those values and blank cells ...
- 21
1
vote
1
answer
27
views
How do I use an arrayformula to autofill a column in Google Sheets?
I have a script that automatically adds a new row to Tab 2 when a row is added on Tab 1. However I want my formulas on Tab 2 to autofill into the row that is added. Here is my formula:
=IF(AND(...
- 63
0
votes
0
answers
14
views
TypeError: Cannot read property 'range' of undefined [duplicate]
function onEdit(e) {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
// EVENT VARIABLES
var range = e.range();
var row = range.getRow();
var col = range.getColumn();
...
- 1
1
vote
0
answers
15
views
How do I perform a VLOOKUP to the left without explicitly naming the left column range in Google Sheets?
I have an array of data that I want to look up a specific phrase from. Once the phrase is found I would like to output the value or phrase of the cell to the direct left of the cell that’s found. I ...
- 21
0
votes
1
answer
17
views
How to find current employee fee based on name and date from a different sheet
I have a spreadsheet with all employees work reports with info about time spent on a job and other details.
I can't figure out how to get the fee the employee is supposed to get paid because I can ...
- 183
1
vote
2
answers
17
views
Array Count by Week in Google Sheets
I am trying to create a running column array that counts the number of occurrences by person by week. It can't be a pivot table or separate array table but a calculated column that updates beside the ...
- 111
0
votes
1
answer
54
views
ArrayFormula and Categorize Data in Google Sheets using Regular Expressions
I'm following this post to categorize data in Google Sheets using regular expressions.
The person who wrote the answer was able to get the formula to automatically write down the category in column D.
...
1
vote
1
answer
30
views
For Google Sheets, what is the function that will populate a cell with specific text when pulling data from multiple tabs of another sheet?
I am building a Google Sheet that pulls data from another Google Sheet with multiple tabs. The source sheet doesn't include one particular data point (i.e. business name), and I want to populate the ...
- 11
1
vote
1
answer
17
views
Is there a way to query and reference a cell that will change?
Alright, looking for some help. I think I am close to the solution, but I could use some help getting it over the finish line. This spreadsheet link is a replicated document of my situation.
...
0
votes
1
answer
19
views
Parsing the result of an array formula inline?
I am trying to use =GOOGLEFINANCE(B2, "high", DATE(2022,7,26)).
According to google's docs, using historical values in this formula will result in an array result...
So, it pumps values all ...
- 121
0
votes
0
answers
30
views
IF and Arrayformula for Google Sheets
=IF(C2=""(vlookup('FBA Returns'!B:J,9,0),vlookup(C2:C'RMA Main Sheet'!D2:H,5,0)))
IF "C" is blank, Vlookup "B" on Sheet 1, IF "C" is NOT Blank Vlookup Sheet 2.
...
0
votes
1
answer
40
views
Calculating lost followers metric
I am trying to calculate the lost followers and new followers per day for my company’s clients. I have "New followers" and "Profile followers" columns in Google Sheets but, in ...
- 1
1
vote
1
answer
19
views
How to find an invoice number in a sheet than an item in the list of the items of the invoice to get the sold numbers of the item
I have a list of invoice numbers in a column. In another sheet I have the same invoice numbers but with items in another column and a third with sold numbers.
I'd like to get this sales data in the ...
0
votes
1
answer
17
views
Connect Participant Scores(Column D) to their id Numbers(Column A) by sorting Columns B and C
I have 4 columns of data. I am trying to connect the Scores to the idNumbers.
Two sets of data. Column A and B are idNumber and Member names.
And Column C and D are Participants and their Scores.
The ...
- 3
0
votes
0
answers
27
views
CAN I do a spill formula for this array formula maybe a nested array formula
This is an example of the formula I am trying to get into a spill. I am unsure how to make the ab3 ab4 ab5 into a range.
=ARRAYFORMULA(COUNT(IF(({
$N$3:$N$105, $AB$3:$AB$105, $AP$3:$AP$105 } > ab3)...
- 63
0
votes
0
answers
27
views
Need help with an array formula to consolidate data
I'm trying to come up with a formula to consolidate information that's being dumped into a spreadsheet from a Google Form. The Form utilizes questions that repeat and allow a user to input multiple ...
0
votes
1
answer
24
views
Finding average based on criteria (non-contiguous cells)
I was wondering if someone could assist me. I am trying to research it myself but have become stuck.
Here is a sample of a Subject Mark-Book. Different units have different grade boundaries attached ...
0
votes
1
answer
18
views
sequential number as long as they are in the same category, but if the category changes, the numbers start over for each category
I have two columns in my Sheet, one for the serial numbers and the other for the category.
I want the numbers to be sequential as long as they are in the same category, but if the category changes, I ...
0
votes
1
answer
31
views
Count number of occurrences in Google Sheets, with multiple criteria and not considering duplicates in the row
I'm looking to count the number of rows that contain a certain text (specified in the adjacent cell), from a specific range, on the basis that the criteria have been met.
I currently have this formula ...
0
votes
2
answers
37
views
How can I create an unique list of data across columns containing text "Retailer Name"?
I have certain data where I want the unique Retailer name across multiple sheets and multiple columns containing "Retailer Name" as the heading. I've shared the editable Google Sheet link, ...
1
vote
1
answer
40
views
How can I shorten this formula in Sheets?
In column A I have a long list of words (one per cell). My goal was to determine which words are made up of the same letters (e.g. Align, Lagging, and Ganglia, so repeated letters don't matter to me).
...
- 137
0
votes
0
answers
24
views
IMPORTRANGE contains specific columns with multiple answers
Example, one person can enter 5 events with several options for participating (both days or Sunday only or 1st go....) how do I get master1 sheet to only show contestants on Saturday and master2 only ...
0
votes
1
answer
111
views
Combine an array formula with query function
I want to know if there is a way that I can combine any of these 3 formulas.
Sample Sheet
I have in col. A3, this extracts the info in Data tab, and splits into rows: =arrayformula(substitute(...
- 191
2
votes
1
answer
539
views
Text result of JOIN is longer than the limit of 50000 characters
Sample Sheet
I want to split a column of data, where there are multiple lines in a single cell. I want these split into rows with a formula.
I've got the JOIN formula set up for that and it works ...
- 191
0
votes
1
answer
24
views
ArrayFormula won't fill down column Google Sheets
I've got a sheet with account numbers in column Q. If the account number is 9 I want it to say "Yay", if not 9, "Boo".
=ARRAYFORMULA(If(Q3=9,"Yay","Boo")
This ...
0
votes
1
answer
47
views
Remove all Array_Constrain(ArrayForumla(*),1,1) formulas when importing from Excel to Google Sheets using RegEx
My first question here. I have an Excel Spreadsheet, where I have not used CSE, which I import into Google Sheets. Upon importing, some 40k+ cells have =Array_Constrain(ArrayFormula(*),1,1) in them. ...
0
votes
1
answer
107
views
How to use ARRAYFORMULA in an IFS statement
Using IFS with ARRAYFORMULA creates an error saying that the:
"IFS has mismatched range sizes. Expected row count 1. column count 1......"
=IFS(A2="A",ARRAYFORMULA(B3:B10),A2=&...
- 3
0
votes
0
answers
22
views
SUMIFS with multiple criteria in multiple columns
I'm trying to take attendance using a SUMIFs formula, but based on data in two columns. For example, I want to total the occurrence of initials "MD" in a range of columns, but only if the ...
- 3
0
votes
0
answers
51
views
IMPORT RANGE PARSE ERROR
I have this arrayformula where I need to match cells in a column from one worksheet to another and I used Importrange to link these two but it's giving me errors and I'm not sure what i'm missing
here ...
- 1
0
votes
1
answer
15
views
Make ARRAYFORMULA cover the entire column?
This causes he headaches every time I use ArrayFormulas in Sheets. It seems that you have to specify the exact length of the range, and can't just use for example X2:X like you can in any other range ...
- 1,093
0
votes
1
answer
23
views
Can anybody please help me create a Query function to extract data from rows to columns (ignoring zero data) and using a drop-down box
I have only started to learn Google Sheets instead of Excel. I find it easier to understand if I have a problem to solve but the Query function quite difficult to understand.
I have table of fruit ...
0
votes
0
answers
20
views
How to lock/link data across a row when using array formula?
I have a sheet with multiple tabs where one tab serves as a master, containing a list of music tracks. This master tab contains information about each track, like duration and BPM (tempo). In another ...
2
votes
1
answer
117
views
Google Sheets - Group/Aggregate data
I would like a formula that groups/aggregates the data of Column A, B, C and D if column A is the same string
The issue is the end of the string in column A with different sizes at the end of the ...
- 87
1
vote
1
answer
60
views
Creating new rows using arrayformula if a cell contains info
I have a list of products (bundles of items, eg a number of different books) on single rows in a spreadsheet.
EG:
product
book 1
book 2
book 3
book 4
Product A
ISBN 1
ISBN 2
ISBN 3
Product B
ISBN ...
- 153
0
votes
1
answer
37
views
Sorting multiple columns using formula
Give a sequence of columns, I want to sort each column individually using formula because of some reason.
Eg.
A
B
C
D
4
7
2
5
1
8
1
3
9
3
4
7
4
Expected output
A
B
C
D
1
7
1
3
4
7
2
4
8
...
- 3
0
votes
1
answer
27
views
Regexextract numbers from string
I have a regexextract formula which takes the year from Column A (column) - however this only works if the year is yyyy-yy format - however some of the years are only yyyy and it returns an error
Is ...
- 87
0
votes
1
answer
84
views
Creating a dynamic 2d array
I have to create variable length sequences from a column along its rows.
Eg- Suppose the column is as below
2
4
3
I want to create a sequence like this
2 | 1 | 2
4 | 1 | 2 | 3 | 4
3 | 1 | 2 | ...
- 3
0
votes
1
answer
39
views
How do I simplify this formula to be expandable?
The Example sheet
The formula:
For all x from 1 to 10, if (Sell <= Buy * x) then x, else ">=10"
I thought I might be able to cheat a little with the sequence function:
=ARRAYFORMULA(...
- 3
2
votes
1
answer
58
views
Filter date, and add blank rows between different time slots
I'm trying to build a reservations spreadsheet for my friend's restaurant where she can input on monthly sheets as resos come in, then see a filtered, sorted summary of any selected day.
Here is a ...
- 21