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
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
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, ...
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 ...
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 ...
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 ...
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 ...
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 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)...
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). ...
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 ...
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 ...
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

1
2 3 4 5
16