Questions tagged [google-sheets-arrayformula]
Use for questions about the use of the Google Sheets built-in function ARRAYFORMULA.
649
questions
0
votes
1answer
16 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 ...
1
vote
1answer
19 views
Apply Concatenate formula to entire column
How do I apply the formula as described here..
https://webapps.stackexchange.com/a/76395/279173
which is this part:
=CONCATENATE(""&LEFT(A1,10)&" ...")
to an entire ...
0
votes
0answers
16 views
Array formula works in the sheet but invalid in conditional formatting
I have a list of time intervals along with their convenience ratings. I wish to highlight intervals rated anything other than impossible which conflict with those rated impossible.
I use start1 and ...
0
votes
1answer
17 views
referencing a column in a Google sheets array literal
Is there a way to refer to columns in a literal array like so:
=FILTER({lengthy_arrayformula_1, lengthy_arrayformula_2}, condition referencing column 2 of the array literal in previous argument)
or
=...
2
votes
0answers
14 views
If there anyway i can check if the cell contains the result of a arrayformula()?
For example i have this in A1 :
=arrayformula(B1:B100)
If in A10 i accidentally enter a value (which will overlap with arrayformula result) , it will produce error in the A1 because of existing value....
0
votes
1answer
31 views
Grab the right most value using arrayformula
I have this sheet :
https://docs.google.com/spreadsheets/d/163jhtdKcC6Pu34jX9brolZDb5bm-PWqG7i-yAtNueoo/edit?usp=sharing
Trying to get the right most value per row
Basic formula :
=FILTER(B2:2,COLUMN(...
0
votes
1answer
13 views
find counta() per row using arrayformula()
I have this table :
https://docs.google.com/spreadsheets/d/1ICEL33X3ADz7XEH2Cmi1p6mrYT2RqPwbtBjCHWoNkoA/edit?usp=sharing
Basically i want to do counta() per row but using arrayformula(). The basic ...
0
votes
1answer
25 views
vlookup with multiple output and combining filter() with arrayformula()
I have 2 sheets : shot and deadline.
https://docs.google.com/spreadsheets/d/1drNUQVtAxd0HU4QCVSKJSsKHoanE735n8HsjkYpYiGs/edit?usp=sharing
This is my shot sheet :
and this is the deadline sheet :
So ...
0
votes
1answer
11 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, ...
0
votes
1answer
16 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 ...
2
votes
1answer
103 views
How can I repeat a value into a range using a formula?
I want to do something like this:
The value can be a character or some words.
If the value is number (for example = 0) then I can do:
sequence(11,6,0,0)
But I can't use a character or words.
0
votes
1answer
17 views
Google-Sheets : ARRAYFORMULA() for multiple columns
I have this table :
https://docs.google.com/spreadsheets/d/100ij18btv5VTmnwwdaFJ8U3PEzkS8Z-duEn2PKgA6sc/edit?usp=sharing
There are 3 Sheets inside it.
The idea is to get how many ingredients used each ...
-1
votes
1answer
18 views
How to combine formulatext() and arrayformula()?
I want to use formulatext() with arrayformula() :
=arrayformula(formulatext(A1:A10))
But The arrayformula doesn't work. It should output 10 rows but it only returns a single row output.
Is there ...
0
votes
0answers
15 views
Array Forumlas and Sumifs in Google Sheets
I have a inventory spreadsheet that I am using to keep track of my inventory and I want to add a new location to track. I added the location into my forms and they populated on the spreadsheet fine. ...
0
votes
0answers
7 views
Sort function in Google sheets shows empty output when sorting on a column with dates
I have a form response sheet with an ArrayFormula to either show the date from a form field or, when this is left empty, the timestamp date.
This column is the master datetime column I want to sort by....
0
votes
2answers
21 views
Print True or False in calculated column using ARRAYFORMULA
I need to output either TRUE or FALSE in column E, based on the following criteria:
If A2 = "stackexchange" then (always, regardless of other data) print TRUE in E2 (otherwise print FALSE)
...
0
votes
2answers
32 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 ...
0
votes
1answer
32 views
Google Sheets, use arrayformula to get average score for each classroom from a list of all students in all classrooms?
Whilst not actually referring to students and test scores, the following hypothetical is the easiest was for me to explain the question.
Sheet A contains a list of all classrooms, sheet B contains a ...
0
votes
1answer
20 views
Apply custom formula across all columns using Array Formula
I have trouble applying my custom formula across all columns using Array Formula. My custom formula is named getCountry where it could get the country based on an address. The address components e.g. ...
0
votes
1answer
18 views
Using Cell Value as a Worksheet Name in Formula
I wanted to use a cell's content as a sheet name, i.e.
A1
A2
A3
A4
South
North
East
West
and use the cell values (South, North, East, West) to reference cells from sheets called South, North, East, ...
0
votes
1answer
32 views
Combine data from multiple sheets to create a stacked line chart
I have multiple sheets with three columns each: Date, Invested Amt, and Return Amt. I am trying to merge the values and get a Result dataset to create a single stacked line chart of Invested vs Return ...
0
votes
1answer
11 views
Google Sheets pulling more data than the criteria specified
I have a tab in my Google Sheet for my raw data. I have another tab which has the report form which includes to criteria fields which I want to be able use to pull the appropriate data from the raw ...
0
votes
1answer
16 views
Trying to combine two (2) statements together
I am trying to pull data based off of selections made in a dropdown list, and would like to combine two (2) statements/formulas into one. Separately, they both work well and achieve what I want them ...
0
votes
2answers
26 views
Can't format hardkey date data as a date in Google sheets?
I've got some date data in a Google drive sheet which is imported from a CSV.
The date data is formatted as either dd/mm/yy or dd.mm.yy as hard key data (eg. not formatted as a date)
I got to format &...
1
vote
1answer
26 views
Google Sheets, using VLOOKUP and ARRAYFORMULA to search multi-line cells
I'm trying to use VLOOKUP to find the batch number of a product based on its serial number. The problem is that the serial numbers for each batch are stored in one cell per batch, separated by ...
0
votes
2answers
31 views
How to make variables dynamic in ARRAYFORMULA/QUERY Combination
I'm trying to self populate the first column of the "Main" sheet with a query that returns the first column of the "Finance Office" sheet that matches all three names.
I came up ...
0
votes
0answers
31 views
What is the Google Sheet query for getting rows excluding non matching rows
The tables will have multiple entries for amount debited and credited details. It also has 5% debit and credit and 1% debit and credit entries as well. Data includes unique flag for districts, Date ...
-1
votes
1answer
13 views
How to apply formula to new rows that are generated by script [duplicate]
I have a spreadsheet where new data into rows is added every hour. I tried using arrayformula but couldn't figure out how.
Example spreadsheet: https://docs.google.com/spreadsheets/d/...
0
votes
1answer
19 views
Google Sheet: Array formula
I made this formula: ={filter(Sheet1!B1:AA22;Sheet1!B23:AA23=SAND);filter(Sheet1!B26:AA47;Sheet1!B48:AA48=SAND)}
(The "=sand" is just "=true" in my language)
But I keep getting &...
0
votes
1answer
18 views
Google Sheets SUMIFS - apply REGEXTRACT to sum_range?
I have the following Google Sheets table and want to calculate the total sums of various medications prescribed:
A
B
C
1
Type
Amount (in g)
Daily dosage (in g)
2
A
60
2
3
B
25 mg/capsule
30 pieces,...
0
votes
1answer
14 views
Return different value for condition in ARRAYFORMULA
I have an ARRAYFORMULA that combines text with a number.
A
B
1
textA
0
2
textB
1
3
textC
2
=ARRAYFORMULA(A1:A3&" "&B1:B3)
But if the value in the B column is 0, I want the ...
0
votes
1answer
12 views
Moving average up until every listed week
I'm trying to automatically calculate a new average every week, so I can see the evolution of the average over the past weeks.
I have one column with the week numbers. The second column contains an ...
0
votes
1answer
14 views
Array Formula Issue
So I have ArrayFormula checking Column C Price to see what range it falls in. Then it assigns the number based on that range it puts it in Column M. It works perfectly as a formula but when I make it ...
0
votes
1answer
36 views
How to have a query wrapped in an arrayformula auto-populate many rows
I have a query that queries over a set of rows. I want to run this query, itself, for each row in another set of rows. The one value that changes in the query for each row is something in the where ...
0
votes
1answer
91 views
Can I implement this moving average with missing periods using ARRAYFORMULA?
I have these columns:
A person_id
B date
C cupcakes_eaten
D date-35 (35 days prior)
There can be multiple rows with the same person_id and same date.
There can be days with zero rows for a ...
3
votes
2answers
125 views
cannot drag query across multiple rows (overwrites data)
Is it possible to click and drag queries?
I describe my problem below and also have a reference sheet.
cells A1:B5
Category
Withdrawal
CatA
4
CatB
2
CatA
6
CatB
11
cells C1:C3
Check For
CatA
...
0
votes
1answer
58 views
Arrayformula with large function for multiple columns in Google Sheets
I was wondering if anyone had a method for finding the second or third largest numbers in a row across multiple columns using the large function in Google Sheets using an arrayformula to account for a ...
1
vote
1answer
74 views
How to convert text to a number in Google sheets
I've built a form that dumps data to a Google spreadsheet and that data, studio reads it... and turns that into a dashboard.. only problem is the text doesn't convert very well for building a dash ...
0
votes
1answer
25 views
How to create a IF formula that searches for multiple (more than three) text options
I'm trying to create a Google Sheet to track pitches (I'm a baseball coach). I have a current sheet that I have a check box to click if the pitch was a strike or not, and then you have to choose a &...
1
vote
1answer
46 views
Google Sheets: VLOOKUP formula for a multi line cells range
I have here a sample project, where columns A to C are in FORM RESPONSES TAB/SHEET and columns D and E are in RECORDS TAB/SHEET.
In the RECORDS I have a list of names, in alphabetical order, and a ...
0
votes
1answer
13 views
attempting to compile an output List, into a single cell in one Sheet, based on matching criteria in a range on another Sheet
I found a proposed solution on Stack, but I can't get it to work.
https://webapps.stackexchange.com/q/120111/270308
Starting formula:
=IF(G3<>""; JOIN(CHAR(10); FILTER(ARRAYFORMULA(...
0
votes
1answer
14 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"...
0
votes
1answer
19 views
Formula for Google Sheet
I need to do a plan for my team.
Name, time of working, and I need a formula that can assign randomly in the slot of 1 hour: chat, call break, and task.
It is a team of 15 people, I can't find any ...
0
votes
0answers
21 views
Max sum held over a month
I need help with a formula that needs to calculate the max sum held at one time for each month.
In this example it would be 54 for March (12+13+14+15, held on the 29/03/2021). Month is determined by &...
1
vote
1answer
13 views
How can I list column names with rows that only contain value
How can I list column names (in one cell) with rows that only contain value (blank not included)?
If possible, separated by a line break.
1
vote
1answer
33 views
Create a Unique Order Number From Existing column of values
I need to create a unique ID from an existing list of order numbers. Pls see the image below:
What I want is for the existing duplicate order numbers to be made unique by adding an extra digit to the ...
0
votes
1answer
26 views
Sum column value if any of several criteria is met in the adjacent column
I have a sheet in which I have 2441 words (hashtags) on their own rows in the first column and in the next (adjacent) column, a number related to them (occurrences of the hashtag in our data).
I want ...
1
vote
0answers
35 views
Multiple columns hierarchical view
I’m trying to display my subcategories in a hierarchical view but can’t seem to figure it out with a formula rather than with pivot tables or scripts
What I have:
A |B |C
Equity|...
1
vote
2answers
80 views
Complex VLOOKUP, Checkboxes and Pivots
I have a situation where room reservations can be managed by checkboxes. You can see an example in the screenshot below. I've consolidated several sheets into one to provide visualization but the ...
0
votes
1answer
24 views
Bulk Search Or Find a bunch of values at once
I have a list of order numbers that I get from one sheet & cross check whether they are there in another sheet (they shouldn't be, they would be the orders that we don't need so we delete them ...
