Use for questions about the use of the Google Sheets built-in function ARRAYFORMULA.

805 questions
Filter by
Sorted by
Tagged with
94 views

I have about 200 google spreadsheets. Each of these google spreadsheet has multiple sheets. I need to gather the data of one of the sheet in all the 200 google sheets to one combine sheets. it is like ...
• 1
1 vote
92 views

### Lookup and Conditional Formatting

I have some columns in sheet 1 (here in image O and P columns), and in another sheet (here in image R, S, T, U, V, W). I want to check the matching Date and Number from sheet 1 with Date and Number ...
91 views

### Google Sheets - Sum Filter of Table 1 in each row of Table 2

I have table of transactions with categories (let's say 1-5) and assigned dollar values, and another table with a list of each category. I want to sum up all the matching categories from Table 1 with ...
• 25
78 views

### How to use data validation based on 2D array or row and column of tickboxes in google sheets?

Here, I'm attaching the part of the sheet which contains the tickboxes. Objective is to format the column A with green or red background colour. Rules: Data in column A should be green, if any of ...
150 views

### ANY ARRAYFORMULA INSTEAD OF SUMIF/SUMIFS

Hope you're doing well. My problem is, I need to SUM all BAL for every ROW in Column B. At this moment I'm currently using =IF(SUMIF(C\$1:W\$1,"=BAL",C2:W2)<>0,SUMIF(C\$1:W\$1,"=BAL&...
31 views

### Get summarised data

I have a table Name Salary Desc2 Desc3 ABC 9 X X ABC 9 X X ABC 9 X X XYZ 4 Y Y PQR 1 Z Z PQR 1 Z Z PQR 1 Z Z I want it something like :- Name Salary Desc2 Desc3 ABC 27 X X XYZ 4 Y Y PQR 3 ...
155 views

### Can I avoid copying this formula in new rows by using ARRAYFORMULA

Suppose I have this sheet showing which group (1, 2 or 3) each student joined on each day of class. Column B indicates in which group he/she last showed up, which is obtained by using the following ...
• 41
58 views

### How to calculate the remaining working days in a month

I have =INT(B8-NOW())currently running in a cell to show me the remaining days in the month (B8 being a date) I want to know if there is a way that the output number can calculate only working days, ...
123 views

### How to structure a vlookup in an arrayformula to accept multiple columns?

I have been working on this problem for a week, where I was looking for the totals of specific row items in a range multiplied by a factor. The formula below achieves the desired outcome. I need help ...
30 views

### How to link 2 sheets, so when a cell on sheet1 is modified, sheet2 got updated automatically and viceversa

I am trying to have the same updated values in two differents sheets of google spreadsheet. So when I edit sheet1, the the values in sheet2 got updated. Also the opposite: when I edit something in ...
1 vote
100 views

### How to tell if a given cell is in an array range in Google Sheets

Some formulas (ARRAYFORMULA, QUERY) can yield values that "spill over" into neighboring cells, as described here: https://support.google.com/docs/answer/6208276?hl=en. When you select the ...
• 111
152 views

### How to pick a custom columns within an array formula

I have next array formula that combines columns from multiple sheets: =arrayformula( { Data1!A2:F, iferror( vlookup(Data1!A2:A, Data2!A2:C, {2,3}, false) ), iferror( vlookup(Data1!A2:...
• 179
172 views

### Array Formula Countif Checkboxes

I'm working on a spreadsheet (in google sheets) for tracking characters' magical abilities. In column b I want to have a count of how many boxes are ticked. Right now it's just the same formula =...
47 views

### Formula always return one value for each cell

I was querying a hyperlink row from one sheet to another using this formula: =QUERY(Data!A2:\$F, "SELECT E WHERE C = 'fragment' ") However I discovered that it copying only text but not the ...
• 179
25 views

### How to replace multiple formulas with single one

I have this formula that I am duplicating for each column within a row: =HYPERLINK(CONCATENATE("http://mysite.com", B2), "url") I am wondering is there anyway to replace that ...
• 179
1 vote
33 views

### Joining multiple sheets horizontally

I have a number of spreadsheets I want to join them horizontally by Id. How would you achieve that? p.s I should mention that the id order could be different in each spreadsheet
• 179
12 views

### How to indicate the source sheet when merging multiple sheets into one

There is a lot of tutorials that show how to merge multiple sheets, so basically there is 2 formulas: ={FILTER({sheet#1-range},LEN({sheet#1-range-first-column})>0); FILTER({sheet#2-range},LEN({...
• 179
1 vote
219 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 ...
• 13
1 vote
74 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 ...
• 53
1 vote
130 views

### Combine different sheets with importrange for dynamic data from google forms [closed]

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
31 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
374 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
232 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 ...
• 195
1 vote
99 views

### How can I return more than one match result from the same search key? [closed]

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
18 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
27 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
1k 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
134 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
152 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
1 vote
46 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 ...
20 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 ...
78 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 ...
• 105
289 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
45 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
166 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. ...
238 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
33 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. ...
34 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 [closed]

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 ...
20 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
96 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 ...
228 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 ...
251 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 ...
57 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
55 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
95 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 ...
402 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(...
• 345
2k 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 ...
• 345
181 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 ...