Questions tagged [google-sheets-arrayformula]
Use for questions about the use of the Google Sheets built-in function ARRAYFORMULA.
805
questions
0
votes
0
answers
94
views
How can I combine data from 200 different google spreadsheets to one master google sheet
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
1
answer
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 ...
0
votes
1
answer
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
0
votes
1
answer
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 ...
0
votes
1
answer
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&...
0
votes
2
answers
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
...
2
votes
2
answers
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
0
votes
2
answers
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, ...
-1
votes
2
answers
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 ...
2
votes
0
answers
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
1
answer
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
0
votes
1
answer
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
0
votes
2
answers
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 =...
- 3
0
votes
1
answer
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
0
votes
1
answer
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
1
answer
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
0
votes
1
answer
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
2
answers
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
1
answer
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
1
answer
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
1
answer
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
- 87
1
vote
1
answer
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
3
votes
2
answers
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
1
answer
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
1
answer
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
0
answers
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, ...
- 11
1
vote
1
answer
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
2
votes
0
answers
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
1
answer
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
0
answers
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 ...
- 21
0
votes
1
answer
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 ...
- 183
-1
votes
2
answers
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
0
votes
1
answer
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
1
answer
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
1
answer
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.
...
0
votes
1
answer
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
0
votes
0
answers
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.
...
0
votes
1
answer
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 ...
0
votes
1
answer
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
0
votes
1
answer
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 ...
0
votes
1
answer
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 ...
0
votes
1
answer
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 ...
0
votes
2
answers
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
1
answer
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
0
votes
0
answers
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 ...
0
votes
1
answer
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
2
votes
1
answer
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
0
votes
1
answer
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 ...
0
votes
1
answer
186
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
887
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