Questions tagged [google-sheets-arrayformula]
Use for questions about the use of the Google Sheets built-in function ARRAYFORMULA.
627
questions
0
votes
0answers
17 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
8 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
17 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
0answers
9 views
How to automatically assign incomplete task to an employee next time he enters his name? [duplicate]
I want my sheet to assign incomplete (pending/left) task next day/next time when the name is entered.
Also when the task is completed I don't want it to re appear next day.
Brief-
On the A column ...
0
votes
0answers
16 views
How to automatically assign incomplete task next day? [closed]
I want my sheet to assign incomplete (pending/left) task next day/next time when the name is entered.
Also when the task is completed I don't want it to re appear next day.
Brief-
On the A column ...
0
votes
1answer
12 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
11 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
11 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
12 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
18 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
88 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 ...
0
votes
0answers
11 views
Distributing a google table between multiple sheets [duplicate]
I have to keep track of number of hours spent on tasks per person. In order to make everything more readable, instead of putting it all in a single sheet, I'm dividing tasks into categories and ...
3
votes
2answers
121 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
26 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
30 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
22 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
36 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
11 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
12 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
19 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
12 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
31 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
23 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
78 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
23 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 ...
1
vote
1answer
34 views
countif but not empty cells
Can I make this formula not count empty cells?
={"Score"; INDEX(if(len(A2:A), countif(if(REGEXEXTRACT(C2:BE,"(?i)yes")="yes", row(C2:C)), row(C2:C)),))}
1
vote
1answer
53 views
Google Sheet split cell to rows - data manipulation
I have a sheet, where there is a list of times in Col. C.
What I want is for it to be split into different rows & also the relevant order number & name to be copied with the rows.
Resulting ...
1
vote
1answer
22 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.
COL 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 to ...
0
votes
0answers
18 views
Trying to Automate invoicing using Google Forms and Sheets
I am trying to problem solve a Google spreadsheet that I use for invoices.
Currently the set up is as such. I have 4 sheets in the spreadsheet. The first sheet is linked to my google form gets its ...
1
vote
1answer
25 views
Google Sheet - filter non-zero or #N/A using custom formula
Say my data is in range A6:Z with the headers in row 5. I want to display rows with value in column C that is non-zero or #N/A. I tried OR(C6:C <> 0, IFNA(C6:C, true)) and OR(C6:C <> 0, ...
0
votes
1answer
41 views
Running Average in google sheets
I'm new to data analytics. This might be a simple solution but I could not find it after 2 weeks of trying. I have a column of numbers that represent wins and losses. Losses are represented as "0&...
0
votes
1answer
47 views
Count if contains yes using array formula for Google Form entries
I want to count every yes per submission of each Google Form, so the row.
Some of the responses have comments after the yes and it won't count those. This is the formula I am using:
={"Score"...
0
votes
0answers
19 views
Query - Procv with 2 Conditions
I'm trying to do a query with 2 conditions:
=ArrayFormula({"User"\A1:F1;QUERY(SE.ERRO({PROCV(B2:B&F2:F;G2:H;2;FALSO)\A2:F});"Select * Where Col1 Is Not Null Order By Col1 Asc")}...
2
votes
1answer
26 views
Using ARRAYFORMULA to do two functions
I have a spreadsheet like this:
4.5
5.0
4.5
5.1
5.1
4.5
And I want to achieve a range like this:
4.5 | 3
5.0 | 1
5.1 | 2
I can achieve this with two formulas UNIQUE and COUNTIF, but - as far as I ...
1
vote
0answers
20 views
How can I use a dropdown menu in Google Sheets filter out data and populate cells for a an expense tracker?
I have created a personal expense tracker (budget) that uses google form responses and categorizes them by fortnights and expenditure categories. This has been successful on a sheet called 'Yearly ...
0
votes
1answer
37 views
is there a formula that will allow me to pull data from one google sheet tab to another based on a if contains conditional
I have a list of start dates in one tab in Google sheets. I want to pull only the start dates that reflect 2021 into a new tab, as well as pull the corresponding info for that entry from the other ...
2
votes
1answer
46 views
Adding together multiple Sum Filter or Sumifs with multiple conditions across multiple sheets?
So I have a budgeting spread sheet. I have an overview page that I need to link to a "Checkings" sheet and a "CC-Chase" sheet.
To calculate the amount spent on a specific category ...
1
vote
1answer
39 views
Combine multiple IF statement/condition into one Google Sheet column
How can I achieve in Google Sheet so that the column will auto populate the correct "vendor" based on the selection on "model" and "size". Refer below:
There will be 2 ...
1
vote
1answer
26 views
How to use a query to find a row of same name but last entry, then copy a value next to it (update request)
(This is an updated version of this question - I am trying my best to make myself clearer this time.)
I am trying to use a query to copy values from one column to another.
Both source and destination ...
0
votes
1answer
16 views
How to extract desired text from a cell
As you can see below I have these data in single cell I want only car nos as my result
1
vote
2answers
34 views
How to autofill date but skip Friday of every week?
I was trying to skip Friday of every week when I am trying to autofill the date in Google sheet.
There are formulas to skip weekends but I couldn't find any formula to skip any specific day of the ...
0
votes
2answers
47 views
Change all unique country abbreviations to country names and place in one cell
I need a formula that can add all the countries with that is in a column.
Example:
In sheet 'Export' I have the below countries of origin:
And in other sheet I need to show all the countries of ...
0
votes
2answers
57 views
How to sort a column with date that is impeded within Texts?
I have a list of files' names that I would like to sort based on the date that's impeded within the file name text (Column A folder). When sorting them, the number 10 comes after 1 instead of 1,2,3,4,...
0
votes
1answer
46 views
Look up rate for specific person in effect on or after a date
In this Google Sheet, I have one sheet, "Transactions", with a rate entry that should be auto populated from a table in the "Rates" sheet. The formula should look up the rate for ...
0
votes
1answer
59 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 "...
0
votes
1answer
38 views
Sheets ArrayFormula with SUMIF
In a 'Records' sheet I have columns of timestamp, a duration, and a category (i.e. 13 minutes of 'B' was done at 18/01/2021 00:00).
In the 'Analysis' sheet I have a field where the Type can be ...
1
vote
1answer
22 views
How to create an average value for every cell which contains a certain phrase?
I need to find the average value of cells containing a certain set of initials to create a scoreboard.
The value of the cell being the score and the brackets containing the initials of the score ...
1
vote
1answer
70 views
How to filter blanks in a table using ARRAYFORMULA
I want to copy down filter formula using Arrayformula. I also gave a hand to copy down add-on but it's not giving required result. Here is a picture of an example sheet:
I am filtering the non blank ...
