Questions tagged [google-sheets-arrayformula]
Use for questions about the use of the Google Sheets built-in function ARRAYFORMULA.
756
questions
2
votes
1
answer
168
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
1
answer
37
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 &...
- 17
1
vote
1
answer
165
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 ...
- 13
0
votes
1
answer
24
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
1
answer
15
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"...
- 387
0
votes
1
answer
26
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 ...
- 11
0
votes
0
answers
26
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
1
vote
1
answer
16
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
1
answer
53
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 ...
- 191
0
votes
1
answer
34
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 ...
- 3
1
vote
0
answers
37
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|...
- 11
1
vote
2
answers
263
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 ...
- 113
0
votes
1
answer
66
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 ...
- 191
1
vote
1
answer
43
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)),))}
- 15
1
vote
1
answer
305
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 ...
- 191
1
vote
1
answer
91
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 ...
1
vote
1
answer
1k
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, ...
- 113
0
votes
1
answer
114
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&...
- 3
0
votes
1
answer
115
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"...
- 15
2
votes
1
answer
52
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 ...
- 599
1
vote
0
answers
31
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 ...
- 11
0
votes
1
answer
57
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
1
answer
78
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 ...
- 23
1
vote
1
answer
73
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 ...
- 13
1
vote
1
answer
78
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 ...
- 137
0
votes
1
answer
22
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
2
answers
391
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 ...
- 11
0
votes
2
answers
1k
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 ...
- 11
0
votes
2
answers
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,...
- 387
0
votes
1
answer
47
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 ...
- 145
0
votes
1
answer
289
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 "...
- 1
0
votes
1
answer
577
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 ...
- 23
1
vote
1
answer
26
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 ...
- 11
1
vote
1
answer
156
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 ...
- 15
1
vote
1
answer
87
views
How to combine these formulas in one conditional formatting rule
I am checking if column S is empty & Column Q="Consultation" then I need to set a rule in a cell to make conditional formatting in cell A1. So these are my steps:
Step 1: in X column: =...
- 387
1
vote
1
answer
45
views
Can a formula cell reference be a variable cell reference?
Assuming I insert every day some rows into Sheet1, this formula stops working and I have to manually edit every day --- can the reference to Cell A2 and Cell E2 be a variable that remains hardcoded as ...
- 75
2
votes
1
answer
35
views
Counting Decimal Values
I am trying to count the total cells that have a single decimal value.
For example:
1.1 (1)
2
1.2 (2)
3.333
1.3 (3)
4
1.4 (4)
5.55555
1.5 (5)
6
1.6 (6)
1.7777777
1.7 (7)
8
1.8 (8)
9.999999999
1.9 (9)
...
- 23
0
votes
0
answers
2k
views
How can I select multiple values from a dropdown list in Google Sheets?
I have created a drop-down list, and I want to select multiple values every time I click on it. Is there a way to do without the script editor?
https://docs.google.com/spreadsheets/d/...
0
votes
1
answer
74
views
Using countifs with Index function and (OR) criteria
I'm using this formula to count rows that has either of the following text (SCD,SCA,Sickl).
First I indexed two columns to be one column (E & G)
Then I asked to count any row that has any one of ...
- 387
0
votes
1
answer
593
views
Arrayformula function keep getting error different sizes
I'm trying to sum rows with specific criteria with the following formula:
=ArrayFormula(sum(countifs(disposition,"Consultation" ,Primary,{"","OB_Gyn","Surgical"}...
- 387
1
vote
3
answers
208
views
How to use counta function with two columns (or)
I would like to use counta function to count rows that are not empty based on column A or B.
In other words.. if either column A or B not empty, it should be counted.
- 387
2
votes
1
answer
82
views
How to split two-line rows into separate rows
Editable spreadsheet here:
https://docs.google.com/spreadsheets/d/1cHf9zC00LymZgb17J746ZUoD-YRG1UvIUQQXVlovBrE/edit?usp=sharing
You can see each non-blank row contains two lines. I'm trying to find a ...
- 23
1
vote
1
answer
40
views
Calculating a total value based on X periods, that takes the previous period into account, without intermediate calculations?
I'm trying to create a simple Customer Lifetime Value calculation in Google Sheets that takes 4 variables:
Income first year
Retention rate (% chance of being a customer the year after)
Discount rate ...
- 115
1
vote
1
answer
53
views
Count number of rows with a specific MONTH in the date of a specific cell and a Boolean condition in another cell
Let's say I have a number of dates and whether they occupy full or half a day:
DATE | WHAT | HALF?
...
2021-4-1 | Something Something | false
2021-4-2 | Something Something | false
...
- 123
1
vote
0
answers
19
views
Adding Button to add points to another sheet - Basketball Live Scorebook
I have created a scorebook sheet in google sheet for tracking live basketball stats.
One sheet with the scorebook table for the team with each player one a row.
I want to have a separate tab for data ...
- 11
2
votes
1
answer
33
views
How to shrink lists of strings so that all consecutive values are represented by "A to D" instead of as "A, B, C, D"
Quite tricky to phrase this question but I'm asking getting times ranges of when people are available in 3 hours chunks. They can answer with any combination of "00:00 to 03:00", "03:00 ...
- 33
2
votes
1
answer
325
views
How do I apply arrayformula on a hyperlink so that I can reference another cell in a different sheet?
I have a column on sheet2 where each cell has a hyperlink redirecting to cells of a column of sheet1.
I have manually gotten the "Link to this cell" and put in under hyperlink to be able to ...
- 21
2
votes
2
answers
148
views
Copy/multiply cell contents based on number in another cell
I have a list of items, Col. B & numbers in Col. A.
I want to multiply/copy the text in cell B based on the number in their relative cell A. I.e. multiply 'B' by the number of times in 'A'
Here's ...
- 191
4
votes
2
answers
3k
views
Check of range is blank in Google Sheets
I want to show the average of a range if any cell is populated.
However, my formula shows the average only if the first cell is populated.
I'm using the below formula:
=IF(ARRAYFORMULA(ISBLANK(A2:C2)),...
- 143
0
votes
1
answer
43
views
Reorganizing data submission on Google Sheets [duplicate]
I currently work at a college and we working to make the process of asking student clubs for information easier. We have created a google form for them to submit their member information, but each ...
- 1