Questions tagged [google-sheets-arrayformula]
Use for questions about the use of the Google Sheets built-in function ARRAYFORMULA.
473
questions
0
votes
1answer
23 views
Increment value based on condition
I'm trying to combine arrayformula with an increment value based on a condition.
Basically I have a column called purpose, it basically has 2 values - Restock and Order. Upon order, column Count ...
0
votes
1answer
19 views
How to fill columns automatically when a new row is added
Been trying this for a few hours, trying to fill down column F G H automatically when a new row is added (my data is on another sheet that's coming in from forms)
https://docs.google.com/spreadsheets/...
0
votes
0answers
15 views
ArrayFormula / SumIf for multiple columns based on dymanic date / Monday through Friday
I have several form responses sheets (that I ImportRange into a common workbook) that need to display a total every Monday through Friday of the week that updates for current week Monday - Friday. I ...
0
votes
0answers
14 views
Combining Duplicate Data Within A Table? | Google Sheets
Table Data:
This is a dulled down version of the Data Table I am trying to filter - which is a massive list of leads.
Basically, I am trying to find a way to combine all rows with similar contact ...
-1
votes
1answer
21 views
Google Sheets - If? ISBLANK? Not sure how to pull this one off
I am trying to find a formula for the following:
If cell A1 has a date in it, copy that date to cell F1. If A1 does not have a date in it, copy the date from cell B1 into cell F1.
I have a receipt ...
0
votes
1answer
16 views
Multiple Unique Values Across Worksheets in Google Sheets
I rarely use spreadsheets, but I thought creating a workbook for a contest would be really helpful. I am not sure if what I am asking is possible without a custom function. I am grateful for any and ...
0
votes
1answer
39 views
Generating a list in Google sheets with checkmark filters
I've been wracking my head over this for a few days now, basically I tried to use a index/match/if/countif setup to try to get a unique list generated based on filters made by checkboxes. (not ...
0
votes
1answer
15 views
Using Google Apps Script in with arrayformula?
I'm using an array formula in Google Sheets to determine when a cell shows 'Expired' based on date.
When the cell shows Expired, I've a script which should transfer rows with Expired to another sheet....
0
votes
1answer
25 views
Array Formula with N/A to print nicely when empty
We use a rather long formula something like:
=ArrayFormula(JOIN(", ",UNIQUE(FILTER(SOURCERANGE!A1:A99999,SOURCERANGE!N1:N99999=A2 ))))
Some cells in our table gave results and they print nicely.
...
0
votes
0answers
27 views
Google Sheets - Query - Sum and group by multiple columns into single column
I have 4 separate column that represents in and out transactions..
Columns
A, B, C, D
a = transaction out (some dollar amount)
b = account type (checking, savings, credit)
c = transaction in (some ...
0
votes
1answer
20 views
Google Sheets SUMIFS issue
I'm trying to do a SUMIFS where one of the criteria has multiple strings in the same column, but the others are just based upon 1 criteria. As you can probably guess I'm having the issue that only ...
0
votes
0answers
27 views
Splitting multi-line cells with formulas - syntax problems
I have a data set, that is constantly updated/changing, that I would like to have one column's data separated into multiple rows (and copying adjacent columns to the rows);
I tried to use a script ...
0
votes
1answer
34 views
Ways to iterate and append a date calculation a set number of times in Google Sheets
Let's say I have a sheet with this dataset:
Start Date Number of Days
3/1/2019 2
From this input set, I want to make the following transformation:
Date
3/1/2019
3/2/2019
3/3/2019
A ...
0
votes
0answers
37 views
Google Sheets trying to combine and un-combine text join
Unfortunatly due to security I cannot share the page, but I am using the following codes:
=ARRAY_CONSTRAIN(
ARRAYFORMULA(iferror(
TEXTJOIN(",",TRUE,unique(IF(($D4=Section!C$2:AW$2),Section!C$3:AW$4,"...
1
vote
1answer
27 views
Concat text from one column with text in non-empty cells in range
I need a formula to concatenate the text from column A with each non-empty cell in B1:Z. The number of rows in the input data will change, so that has to be accounted for dynamically. Also, the rows ...
1
vote
2answers
41 views
Find and place in a column, the first non-blank cell in each row from a range of columns
I'm attempting to create an ARRAYFORMULA that can extract the first non-blank cell value on each row in a range of columns.
My latest attempt is this:
=TRANSPOSE(QUERY(TRANSPOSE( A2:D6),"select * ...
0
votes
2answers
40 views
Average of only the numeric part of cell values when some of the cells contain text and numeric values
I am trying to figure out a formula that will allow me to find the average of a group of cells, where some of the cells only contain numeric values, and some cells contain both numeric values and text....
1
vote
1answer
31 views
Create list of cell addresses contained in a range
I'm looking for a single arrayformula to convert a specified range, such as B2:D4, into a list of addresses (not values!) contained in that range, in this case B2,B3,B4,C2,C3,C4,D2,D3,D4.
Attempts ...
2
votes
3answers
53 views
Find and extract words/names from a string beginning with specific character
I've got some rows in Google Sheets that contain strings. I want to extract certain parts from this string and enter them into another cell:
specifically, I want to extract words/names that begin with ...
0
votes
1answer
11 views
Add year (multiple different years) in cells that have only day and month (Google Sheets)
I have a spreadsheet as follows in column B:
10/11
27/10
05/05
01/01
24/12
11/07
03/03
27/11
15/10
The top ones are always from the year that we are, in this example it's 2019, but according to what ...
1
vote
1answer
27 views
Convert ARRAYFORMULA output from 2D to 1D range
tl;dr
How to create an 1D range from a 2D range output of an ARRAYFORMULA.
Intro
I have a Google Sheet that uses a very complex ARRAYFORMULA to perform data validation on about fift cells in a row. ...
0
votes
1answer
60 views
How do you interpolate data in a horizontal range that's non-linear?
I've been told what I'm hoping to do is called 'spline interpolation'.
I've imported monthly youtube channel viewership figures from google API. And I've layed out the dates and views in a horizontal ...
0
votes
1answer
29 views
Incorrect and somewhat strange results when using COUNTIFs with Arrayformula
I have three functions responsible for checking if I own a stock (by seeing that the column F is not empty) Then I want to check column AB to see what market the stock is from. In the first case I ...
2
votes
1answer
28 views
How to automatically calculate averages from ID in Google Sheet?
I've been looking around for an answer but couldn't find what I was looking for.
The problem is that I want to use an arrayformula, but can't figure out a dynamic formula that:
Looks at the unique ...
0
votes
0answers
17 views
How do I copy a set of data items from another tab based on highlight conditions?
I'll just give you a brief idea of what I'm trying to do with my limited logic. I'm pretty certain this could be done in a much better way.
This is for my Instagram account and I'm trying to do the ...
0
votes
1answer
20 views
Google Sheets: Formula to get data from other tab
I have a Google Sheet to get an overview of all workshops/events we are hosting.
There is a tab INPUT. There is a column for Date, Month, Event, Location, Presenter, Language. The column month is ...
0
votes
0answers
27 views
Google Sheets Array formula
=ArrayFormula(IF(ISBLANK(Input!$B4:$B)," ",(INDEX(Input!A4:A, SMALL(IF(COUNTIF($A$3,Input!$B4:$B), MATCH(ROW(Input!$B4:$B), ROW(Input!$B4:$B)), ""), ROWS(A6)), COLUMNS(Input!B4:B)))))
The result is ...
2
votes
1answer
91 views
How to extract multiple paths from a single string of URLs in Google Sheets?
How can I get a subrange of second to last element in a formula under Google Sheets?
I'm trying to extract all occurrences of https://example.com/* in a cell which may contain multiple URLs scattered ...
2
votes
1answer
157 views
How can I use a formula to split a text into individual characters, regardless of length?
I have a list of words in Japanese. I want to write a formula that will input the word, e.g. ひらがな, and output it as an array of however many characters: ひ + ら + が + な, no matter the length of the word....
1
vote
1answer
23 views
How to calculate the following mathematical expression in Google Sheets?
I am trying to calculate the total money that can be made from dollar cost averaging every year using Google Sheets. For example: how would I evaluate this?
2
votes
1answer
43 views
Getting #DIV/0 error in Google Sheets for AVERAGE, SUM, COUNT, etc on column generated by ARRAYFORMULA
I am trying to get the average value of a column (column C "Profit Margin" in the screenshot) in Google Sheets. For some reason, I get a #DIV/0 error when trying to use any sort of range functions ...
1
vote
2answers
63 views
Can I use arrayformula in Google Sheets to count things over a variable range?
Is it possible to create a single arrayformula that does the same thing as the following column of formula:
A1: =COUNTA(B1:B1)
A2: =COUNTA(B1:B2)
A3: =COUNTA(B1:B3)
...
A9: =COUNTA(B1:B9)
My best ...
1
vote
2answers
93 views
Take cells from a column, populate in two different columns following a pattern & delimiters
To better explain what result I need, I made a text block below as a pseudo sheet to illustrate.
Col A (source) is the manually entered text data. Col B and C (desired result) will have formulas so ...
1
vote
1answer
92 views
Adding missing dates to Google Sheets
I'm pulling data from HubSpot to Google Sheets via 3rd party connector. I have a raw data set on Sheet 1 and then I'm pulling certain data from Sheet 1 to other Sheets with QUERY function.
For ...
1
vote
1answer
22 views
Output counted list with extra qualifier
I currently have a table with the following columns:
Comm ID Item Name Months Orichalcum Tempest Jade Green Iron Weltsilver Ambergelt Beggar's Lye Dragonbone Iridescent Gloaming ...
1
vote
1answer
62 views
Filter the row containing an exact word and highest value in Google Sheets
I have a table (Master sheet) in Google Sheets that looks something like this:
https://ethercalc.org/t8f0ze0kyx6a
I need to filter out an entire row of information on a separate sheet (Last Lesson) ...
2
votes
1answer
38 views
How to sum the values of same vlookup results?
I have this formula it gets the single data from another file from a sheet at column 7.
=ARRAYFORMULA(
if(
len($A2:$A);
iferror(
vlookup(
...
1
vote
1answer
166 views
How to automatically fill missing data in Google Spreadsheet
I'm searching for the exact same thing as Google Spreadsheet: How to fill in missing values (inbetween filled values) automatically
I made a spreadsheet for tracking weight, where each row is one day,...
2
votes
1answer
26 views
How to sum amount with a criteria separately by day in Google Sheets?
I am using Google Sheets to track income and expenses. I have a separate file for each month and a master file to summarize each month.
The file for monthly account contains a couple of sheets - each ...
0
votes
0answers
71 views
Multiple filter Array formula in google sheets
I am trying to get my array formula to be able to filter on multiple drop down menus. I have 2 separate formulas that work but I cannot combine them. Is there a way to combine them to make the Array ...
0
votes
1answer
56 views
Sumproduct within Arrayformula [duplicate]
This is my formula in cell D2:
=SUMPRODUCT(B:B=B2;F:F>F2)+1
The values in column are all of the same length, and are continually updated by contributions from google forms. Therefore a condition ...
1
vote
2answers
22 views
Use Google Sheets to create column C by deleting any phrases that contain word found in column B in column A
I have a list in column A and I want to remove any cells that contain any word.
0
votes
2answers
67 views
Extracting last match from a string with REGEXEXTRACT
I've created a simple text filter:
=IFERROR(REGEXEXTRACT(A1;"- May 2019| - Jun 2019|- Jul 2019| - Aug 2019|- Sep 2019"); FALSE)
It should extract the month and year for an event from the cell A1. A1 ...
0
votes
1answer
31 views
How to find the last value in a column when the column is populated from an ARRAYFORMULA
Some columns in my sheet are filled manually, but then two columns are created dynamically from an ARRAYFORMULA (i.e. there is nothing in the column if the manually populated cells are empty, but once ...
0
votes
1answer
26 views
Merging cells based on same ID but divided into categories (columns)
I receive a pretty extent list of material that has been delivered and every single product is in a separate row. That means for one order I have easily a lot over 15 rows and I need to merge the ...
0
votes
2answers
30 views
How can I make sure an equation doesn't give an answer if more than one cell is blank?
I have half the equation working:
=IF(AND(IF(NOT(ISBLANK(AG22)),TRUE,FALSE),AJ22<AG22),"1","0")
So it looks like this:
What I WANT it to do, is to only prompt an increase or decrease response if ...
0
votes
2answers
27 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 ...
0
votes
2answers
36 views
Using TODAY function with QUERY
I am using this formula (see below) to try and retrieve dates automatically between the current date and the next. However, I keep getting the error, "QUERY completed with an empty output."
=...
0
votes
1answer
24 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 ...
1
vote
2answers
25 views
Searching a list and replacing value if any item matches any item in the other list in Google Sheets
I have a list of usernames on two different sheets. I need to scan the first list of usernames to see if they appear in the second, and if they do, replace them with a predefined username. I can't ...
