Questions tagged [google-sheets-arrayformula]
Use for questions about the use of the Google Sheets built-in function ARRAYFORMULA.
809
questions
0
votes
1
answer
2k
views
Increment number in array formula based on a condition
I have a data set with Sub-Total rows at various intervals. As the data can have rows inserted or deleted, I am trying to use an array formula to give each group of data an incremented integer as a ...
CommunityBot
- 1
0
votes
1
answer
426
views
ARRAYFORMULA - Return of Text if cell is not blank and DATE earlier than TODAY
After some unsuccessfull workings hours, I definitely need your support.
I am trying to create an ARRAYFORMULA, so that new rows can be added manually with the wished formulas already implemented in ...
CommunityBot
- 1
0
votes
1
answer
96
views
Reference "This Cell's Column" in Google Sheets
https://docs.google.com/spreadsheets/d/1LG7AgcumhfQ1ZV0Em_oVjdPdrLCP-5CWWrw0WV1oCnY/edit?usp=sharing
(We have to track 3 separate point totals for everyone who has participated in each game, game ...
CommunityBot
- 1
0
votes
2
answers
22
views
Look for a value that might be in many columns
Say I have a value in $A, to match to another sheet of the same file. That value (in this case, a student id) might appear in many columns of the other sheet.
In the following example, I am looking ...
CommunityBot
- 1
0
votes
1
answer
66
views
Create sequential dates with each date repeated multiple times
For every day of the year, I need the day repeated in 10 rows. How can I accomplish this efficiently?
e.g.
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/01/2019
01/...
CommunityBot
- 1
0
votes
1
answer
21
views
Return Average of a column if
Looking to return average if criteria is met, but I can't get the formula to work when one of my criteria exists in multiple columns. Does anyone have an efficient solution?
=AVERAGEIFS(Sheet2!D:D,...
CommunityBot
- 1
1
vote
1
answer
59
views
How to auto-calculate without a need to drag down the formula?
How can I fix formula to a row so that when data is inputted in any cell within that column, it auto-computed in Google Sheets? the % doesn't work
Without having to drag the formula down.
Example:
...
CommunityBot
- 1
1
vote
2
answers
1k
views
How to remove duplicate rows based on a column (leaving first one out) in Google Spreadsheets
I am trying to remove duplicates from my Google spreadsheet file.
Here is an initial table example
A B C D
HelloWorld1 2 6 41
HelloWorld1 4 3 5
HelloWorld2 6 46 6
...
CommunityBot
- 1
0
votes
1
answer
96
views
Add fields and update array and scripts
I want to add some questions to my Google form but my response sheet has a script and an array, which do not like that.
Here is what it should look like:
The URL is the link for the form.
Here is ...
CommunityBot
- 1
0
votes
1
answer
1k
views
Auto-populate cells based on previous cell values in Google Sheets
I have two columns with information. Values in Column B have been manually added to match the values in Column A.
Is it possible to have a function that auto-populates column B with the right values ...
CommunityBot
- 1
0
votes
1
answer
33
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 ...
- 12.6k
2
votes
1
answer
619
views
Average the last N values in a Sheets column using Arrayformula
Experts, I need your help...
I'm looking to create a formula (not necessarily an arrayformula), that will automatically calculate an average. This sounds easy, but surprisingly, it isn't.
Let me ...
CommunityBot
- 1
0
votes
2
answers
245
views
How to filter after parsing CSV values contained in Google Sheets cells
I would like to be able to match all cells in column A (titles) that have one of the comma-separated values in column E (Key Search Terms). This will involve at least two steps. 1) Parse each of the ...
CommunityBot
- 1
1
vote
1
answer
1k
views
Pull unique values from other sheets as new sheets keep getting added
I'm trying to get a unique list of values in a master sheet based on a list of values that appear in column A of other sheets. The tricky part is that every time a new column is added in the master ...
CommunityBot
- 1
0
votes
2
answers
55
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 ...
- 43.9k
1
vote
1
answer
22
views
How to add arrayformula function to this formula with IF function?
I made a table that has two columns (namd & State). The 4th column(check) makes conditional formatting possible for any of these combinations: (if name is either "Ali" or "John"...
CommunityBot
- 1
0
votes
2
answers
2k
views
How do I "bin" results in Google Sheets?
I am trying to bin my results so my groupings have lower granularity - going from a 1-10 scale to a 1-5 scale.
For example, any result 1 or 2 would be renamed to 1 in the next column, any result 3 or ...
CommunityBot
- 1
1
vote
1
answer
127
views
How to use arrayformula to find and get data from only a certain, dynamic set of columns?
I have a table where my headers are dynamically generated from another source and can show up with any header values, in any order.
In the example below I've got the header values:
AA - BB - CC - ...
CommunityBot
- 1
3
votes
2
answers
6k
views
How to pull last value in a Google Sheets column and populate it across another worksheet column?
This is my first post here- I hope this is the right place for it, apologies in advance if it isn't.
Context:
I have a password protected presentation that I need to give users access to, but only ...
CommunityBot
- 1
0
votes
1
answer
62
views
"Multiply" (Concatenate) Text Columns in Google Sheets
Simple question that I can't seem to find a solution for. I have text data in two columns like
Name
Num
a
1
b
2
and I would like to produce a single formula if possible to output a 3rd column
...
CommunityBot
- 1
1
vote
2
answers
143
views
Finding all word pairs from range of strings in Google Sheets
Let's say I have a column of values, each of which is a string.
A
This is a string
This is longer than that
My favorite is short
But sometimes short is longer
How is a short longer
I would like ...
CommunityBot
- 1
2
votes
2
answers
64
views
Spreadsheet formula to calculate most common items in ordered items lists
I am searching for a correct google spreadsheet formula to go through the list of ordered items and summarise the resulting winning items.
Like is you have an Oscar ceremony and you get votes from ...
CommunityBot
- 1
1
vote
2
answers
125
views
In Google Sheets, analyze cell content, and if certain values are present, return a response in the next column
In the below example, I want to analyze each cell in column A, and if the cell's contents match certain parameters, return a value in column B accordingly.
So if the value in a col A cell = apple, ...
CommunityBot
- 1
0
votes
1
answer
486
views
How to solve Error message in Google Sheet array formula: The resulting array was too large
I tried to use this formula below on my Google Sheet for around 10,000 row data:
=ARRAYFORMULA(IF(LEN(L3:L),M3:M&" - "&MMULT((M3:M=TRANSPOSE(M3:M))*(ROW(M3:M)>=TRANSPOSE(ROW(M3:M))...
CommunityBot
- 1
0
votes
1
answer
197
views
How to assign country based off list of either provinces or states?
I have a list of names and locations. Most of the locations include the name of the province or state. Some only include country names, some include state abbreviations.
I also have a list of ...
CommunityBot
- 1
1
vote
1
answer
24
views
How to use ARRAYFORMULA to remove values that contain brackets?
I am trying to write a formula that pulls all data from column A that doesn't contain brackets.
Source Data
Desired Result
(no values containing brackets)
- 12.6k
1
vote
1
answer
3k
views
Remove blanks in import range of multiple arrays
I have an importrange formula as below which references multiple ranges, calling the sheet URL's using the 'L Links'!Bx, reference, each 'book' having an identical sheet set. (There are actually 90 ...
CommunityBot
- 1
2
votes
1
answer
201
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 ...
CommunityBot
- 1
1
vote
1
answer
933
views
Using array formula in Google Sheet
Trying to make an ARRAYFORMULA that executes a query function which takes a reference to the current position of the row number and the specified column name.
This is my version of formula:
=...
CommunityBot
- 1
1
vote
1
answer
58
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 ...
CommunityBot
- 1
1
vote
1
answer
14
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 ...
CommunityBot
- 1
0
votes
2
answers
873
views
Is there away on google sheets to paste text and automaticly create new rows with every line break or etc?
Is there away on google sheets to paste text and automaticly create new rows with every line break or ect?
I will paste a lot of text by this format in the example below (700 lines in total):
...
CommunityBot
- 1
0
votes
1
answer
130
views
How to assign a name to a membership number vice versa?
I would like to learn what formula I can use to make my timesheet more efficient.
How to assign a membership number to a name vice versa? I need to add lesson hours to each membership daily and ...
CommunityBot
- 1
0
votes
1
answer
146
views
Create a list of names of those who answered a question correctly
I have a finished Google Form connected to a Google Spreadsheet - I want to list the names of everyone who answered a specific question correctly. I've spent a while trying to figure out how to do ...
CommunityBot
- 1
2
votes
2
answers
421
views
How do I get the count number of weekdays in a datetime column in Google Sheets?
I have a timestamp in mm/dd/yy hh:mm:ss format from January to March. How do I count "how many days are Monday" in the range of A1:A199? (Or How Many are Tuesday, Wednesday, Saturday?
This gives me a ...
CommunityBot
- 1
1
vote
1
answer
975
views
How to ignore certain strings when creating unique list of words in Google Sheets?
In Google Sheets, I am using the formula to create a unique list of words:
=unique(transpose(split(ArrayFormula(concatenate(B2:E"+")),"+")))
But I want to filter out certain strings.
My unique list ...
CommunityBot
- 1
2
votes
4
answers
5k
views
How to use max() with arrayformula()
This is my sheet :
in column E, i want to find the max among col A,B,C using this simple formula in E1 :
=max(A1:C1)
But rather than pasting the formula to E2 .. E10, i want to see if possible to ...
- 12.6k
1
vote
1
answer
95
views
Fetch value of a cell based on dropdown menu items results on another sheet
I have 2 Sheets named as Sheet1 and Sheet2...
Sheet1: Contains Drop down list on B4 Column. Based on the selection of an item, it pulls the result of the open price shown on D4 column.
Sheet2: ...
CommunityBot
- 1
0
votes
1
answer
570
views
How to use FLATTEN function in query function
Below in my query result:
In the above table "Form Response" table is the response received in Google Sheets from Google Form.
I want to convert "Form Response" table to "...
CommunityBot
- 1
1
vote
2
answers
37
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 ...
- 12.6k
1
vote
1
answer
58
views
Value transformation in Google Sheets [closed]
I'm working with a table of data as following:
User ID: Event name:
111 Event 1
222 Event 2
333 Event 3
111 Event 4
444 Event 5
111 ...
CommunityBot
- 1
0
votes
1
answer
676
views
Summing hours spent on a subject in Google Sheets' "Assignment Tracker"
Hi everyone!
I love the idea of this Assignment Tracker in Google Sheets. But my goal for time management is to find out how much time I'm spending on each subject.
Is there a way to automatically ...
CommunityBot
- 1
1
vote
1
answer
41
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 ...
- 283
0
votes
1
answer
58
views
Auto sorting / copying data based on inputted data?
Sorry if this is a bit confusing or if I’m not explaining this well enough. Using my provided picture as an example how would one auto copy data that gets inputted into that table based on estimated ...
CommunityBot
- 1
0
votes
1
answer
19
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 ...
CommunityBot
- 1
1
vote
1
answer
73
views
SUM across other worksheets (without hardcoding their names)
I've got a spreadsheet for my organisation's management accounts. Each project has a worksheet, setting out income and expenditure by financial year. This is aggregated together in a few sheets ...
CommunityBot
- 1
0
votes
2
answers
36
views
Google Sheets: Make the cell data the name of the link
Update:
I have attached a pic for example.
--------
I have ID numbers in each cell and I want that number to be a link to their file.
I know how to make a separate hyperlink that will ref that cell to ...
CommunityBot
- 1
1
vote
2
answers
158
views
Can we do arrayformula for repeating horizontal join?
I have this table :
where i want to join all data in each row start from column D to last column. I want the output just like in that C2:C5
I use this formula in C2 :
=JOIN(",",filter(D2:2, ...
- 12.6k
0
votes
1
answer
385
views
Having trouble outputting GOOGLEFINANCE data
My wish is to create a table in Google sheets looking something like this:
price
day A B C D ... Z
1997-01-02 132.54 . . . .
1997-01-...
CommunityBot
- 1
1
vote
1
answer
119
views
Why my Arrayformula doesn't work
I have this google sheet :
link is here :
https://docs.google.com/spreadsheets/d/1_-AcgJud2DclKtarcJj39DNB84CzthUzO_n5F8mBgKY/edit?usp=sharing
On the top is my matrix table. It has key on the left ...
CommunityBot
- 1