Questions tagged [google-sheets-arrayformula]
Use for questions about the use of the Google Sheets built-in function ARRAYFORMULA.
591
questions
0
votes
1answer
31 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
41 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
31 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
21 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
12 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
31 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
34 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
56 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
40 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 ...
-1
votes
0answers
17 views
How do you combine/concatenate within the same column with conditions on Google Sheets [duplicate]
Will explain it with picture example, lets say I ran a car repair with this Google Sheets:
And I want to combine/concatenate All Column B into column D like this
with Car Number as Conditions using ...
0
votes
1answer
25 views
How to use FLATTEN function in query function in Google Sheet
Below in my query result:
In the above table "Form Response" table is the response received from Google Form.
I want to convert "Form Response" table to "Desired Result" ...
0
votes
1answer
29 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
49 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 ...
0
votes
0answers
12 views
Array formula-ing this formula breaks it…any ideas why?
I have a spreadsheet with SheetA and SheetB. I'm trying to pull into SheetA some of the columns of each row in SheetB where the date in Column I of SheetB is earlier than today.
This formula on a ...
1
vote
1answer
54 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: =...
1
vote
1answer
35 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 ...
2
votes
1answer
26 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)
...
0
votes
0answers
34 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
0answers
20 views
How do I control what gets iterated in an arrayformula in Google Sheets
This works:
L2=average(filter(W_Max,regexmatch(W_Date,A2)))
W_Max and W_Date are named ranges with W_Date being D2:D51147, and W_Max being F2:F51147. I get the average of all the W_Max values where ...
0
votes
1answer
32 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 ...
0
votes
1answer
54 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"}...
1
vote
3answers
60 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.
2
votes
1answer
59 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 ...
1
vote
1answer
26 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 ...
1
vote
1answer
12 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
...
1
vote
0answers
16 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 ...
2
votes
1answer
30 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 ...
2
votes
1answer
38 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 ...
2
votes
2answers
113 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 ...
1
vote
1answer
172 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)),...
0
votes
1answer
22 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
vote
2answers
33 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 ...
1
vote
1answer
50 views
How to filter Queried Data in an Array
Sample Spreadsheet: https://docs.google.com/spreadsheets/d/16p4M5UBODyBhmWFXYrA0FdLsA1FmwSmbrF12VUHlGaw/edit?usp=sharing
I have this formula which works great:
=ARRAYFORMULA(QUERY({Projects!A:C, (...
0
votes
1answer
39 views
Copy data based on date to another cell but other column date is not exact match
I want to match the dates in Column A to the Closest date in Column E (if a date in column A doesn't match to dates in Column E then I want to return the next available date in Column E and I've done ...
0
votes
1answer
28 views
ArrayFormula to calculate stock position by date
I am creating a stock transaction tracking sheet where I enter buys and sells etc. I have a formula that can calculate a running position total but I cannot figure out how to convert it to an array ...
1
vote
1answer
25 views
Trying to calculate how frequent of the shift happen in certain criteria
I work on schedule, would like to calculate how many are morning shift and how many are afternoon shift base on market.
I've tried if with countifs, arrayformula with countifs, it doesn't seem to work....
2
votes
1answer
69 views
How do I extract specific value from a single cell on Google Sheets
I'd like to get respective data into each cell without the key and punctuations. Each cell must display only the value extracted from the properties column.
I've tried to go to Data>split text to ...
0
votes
0answers
22 views
How to convert multiple form responses into easily readible table to use
I have a Google Form that is going to be used by different employees to add stock received. Since there is usually more than 1 item being received, I needed to provide space for them to add multiple ...
0
votes
3answers
73 views
=IMPORTHTML forces content to date format
I am a beginner with code and formulas here, so I was wondering if anyone could do exactly what was done on this question:
Trying to use Google Sheets importHTML() to import a table. It forces content ...
1
vote
1answer
23 views
Replacing items in comma delimited cell with same string each time
I'm trying to create a formula that will replace any contents of comma delimited cell with single string. The number of items in delimited cell will vary...
Specifically:
524352435,2345235523,...
2
votes
2answers
64 views
How do I find the max value in a column only if it contains certain texts in google sheets?
The data has numbers in text format and texts with numbers.
How do I find the max of the numbers in text format without converting to numbers?
How do I find the max if it contains certain texts (ie, ...
0
votes
1answer
20 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):
...
0
votes
1answer
22 views
How do I include blank cells when using an Array Formula?
I have a Google form that I'm using to collect info for user account creations. I wanted to make it so that the person filling out could just let me know about multiple users at an office in one entry ...
1
vote
2answers
58 views
Google sheets, convert value from 1 column to another based on particular data in it
Let's say I have those values in the column:
w 1
w 2
w 3
I want to convert those values to
1
2
3
in another column.
So basically I need to convert the value "w x" to "x".
How ...
1
vote
1answer
43 views
How do you perform a bulk find & replace using a list of search phrases and their replacements?
This is not my wheelhouse and I'm realizing I'm in a bit over my head. Any help would be greatly appreciated.
What I'm trying to accomplish
https://docs.google.com/spreadsheets/d/...
1
vote
1answer
108 views
Search multiple columns for one value
This is roughly what my table looks like:
A B C D E F
Name |Ingredient 1 |Ingredient 2 |Ingredient 3 |Ingredient 4 |...
0
votes
2answers
69 views
Google sheets array formula for taking average every Sunday and output is in same row corresponding column
I would like to take the average using an array of every 7 days on a Sunday and place the output on the same row but corresponding column of that Sunday.
If a cell in B is left blank then it should ...
1
vote
1answer
31 views
Is it possible arrayformula combined with many if, and, isblank?
Is it possible arrayformula combined with many if, and, isblank ??
The code below works fine
=IF(AND(ISBLANK(F8)=TRUE,ISBLANK(H8)=FALSE), "Not shipped", IF((AND(ISBLANK(F8)=FALSE,ISBLANK(H8)=...
0
votes
1answer
23 views
Calculating monthly average from daily average (grouped by month)
I have a spreadsheet with three columns: one for dates, one for daily income and one for daily change rates.
I want to have a sum of all the daily income grouped by month as seen above. For that, on ...
