Questions tagged [google-sheets-arrayformula]
Use for questions about the use of the Google Sheets built-in function ARRAYFORMULA.
488
questions
0
votes
1answer
26 views
More compact version of nested substitute function possible?
I want to make about a hundred changes in a column on a spreadsheet. These changes have to be remade each time a new copy of the original comes. The changes are spread over several thousand rows.
...
0
votes
0answers
15 views
Dynamically retrieve entire row in Google Sheets for computation
Objective:I want to retrieve the content of the last row of a Google Form and do a simple computation with that and later return that value to the user.
Example:
The form contains two questions: "How ...
0
votes
1answer
16 views
Google Sheets Formula - Trying to Sum, based on multiple cell criteria
I am looking for help with Google Sheets formula.
The formula I need is -
If A:A=LG and F:F=Indexed then sum the total of P:P for all of those instances.
If A:A=PG and F:F=Indexed then sum the ...
1
vote
2answers
56 views
COUNTIF with relative cell range in condition
I want a Google sheets formula along the lines of
=countif(a4:a150;and(sum(e4:j4)<132;sum(e4:k4)>=132))
I.e., for each of the rows 4-150 (column A is not relevant), if the sum of columns E-J ...
0
votes
0answers
25 views
Pull data for different sheet and keep formulas in new rows
I'm trying to accomplish 2 things at once. I have 2 pages with customer information, one page being contact info and another being CRM information based on those customers.
I'm trying to populate ...
1
vote
1answer
30 views
Google Sheets - Identify if one of many matches any in an array
I am trying to determine if an array of values in Google Sheets has any matches within another array of values. The data set is constantly expanding, so I want to use an Arrayformula to handle it.
...
1
vote
0answers
16 views
Summarize all data from other tabs with same format and column
I have customer sheets which i must summarize into a single tab so i use this query;
=QUERY({'ROC155'!B2:AB;'ROC152'!B2:AB;'ROC135'!B2:AB;'ROC002'!B2:AB;'ROC009'!B2:AB;'ROC145'!B2:AB;'ROC048'!B2:AB;'...
0
votes
0answers
19 views
Formula is not working when adding ArrayFormula
I am making a 3d Print Log so I can keep track of my prints, their Material cost.
EDIT: Seems part of my post got deleted when I submitted. But your suggestion worked. Thanks so much!
=ARRAYFORMULA(...
1
vote
1answer
27 views
Formula Error: =IF( SEARCH() ); Visual Reference to Question
I know I'm very close with my formula: =IF(SEARCH("Wednesday", (A3:A)), "workday", 0),
But I must be missing something here...: I can't get this formula to display the value "-" when I run it on a ...
1
vote
1answer
27 views
Query/Filter function finding the closest date that is less than the order date for an array of dates using a given variable
Overview
I need to get the cost of something by comparing item dates against an order date for one or more of the same item with different item dates, and making sure to select the item date that is ...
1
vote
1answer
26 views
If “text” then, how to put a conditional formula
There are three columns. A, B and results.
I need to make a formula that gives the following:
If the text in both columns A and B is WIN, then in the results we should see: Winner.
If the text in ...
1
vote
2answers
69 views
“Error Did not find value '____' in VLOOKUP evaluation.”
Please see my public spreadsheet here:
https://docs.google.com/spreadsheets/d/11RMN7uN_woEQVzlDEkX6vSARTCGYYBjWs11ZDU-6Shc/edit#gid=1276951687
My goal is to get the Location Code from tab 1 into tab ...
2
votes
1answer
48 views
COUNTIF with Multiplicities
I am currently creating a histogram on Google Sheets to count how many of x-product in our inventory we have priced at less than $10k, between $10k to $20k, $20k to $30k, etc.
The problem is that ...
1
vote
1answer
21 views
Search for missing data starting with certain text
Hello I need to compare two columns and output all missing data into a third column.
The problem is that I need to also filter the first column for only certain cells that start with two letters
...
0
votes
1answer
28 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
24 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
16 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
20 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
26 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
26 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
43 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
23 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
35 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
22 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
28 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
35 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
38 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
29 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
46 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
43 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
32 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
55 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
31 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
71 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
32 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
27 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
37 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
141 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
160 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
25 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
84 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
100 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
94 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
120 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
69 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) ...
