Questions tagged [google-sheets-arrayformula]

Use for questions about the use of the Google Sheets built-in function ARRAYFORMULA.

Filter by
Sorted by
Tagged with
0
votes
0answers
16 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
14 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
53 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
24 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
26 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
50 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
46 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
27 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
25 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
42 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
22 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
34 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
21 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
28 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
42 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
30 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
68 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
134 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
78 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
98 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
116 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) ...