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

145 questions with no upvoted or accepted answers
Filter by
Sorted by
Tagged with
677 views

### CountIf in upper cells with ArrayFormula

I have some values in the column A and in the column B want to calculate concatenation of the value and how many times it was used in rows above. In the usual formula, I would've written in B2 =A2 &...
97 views

### How to remove duplicate words in a cell only if longer than a certain number of characters?

I need to remove a duplicate text in a cell but only if it is longer than 4 characters. I have this formula that only works for any types of duplicate words: =join(" ",unique(transpose(split(A1,", ")...
41 views

### What's wrong with this arrayformula?

=arrayformula(if(LEN(A:A),if(ROW(A:A)=1,"Outstanding amount Payment", sum(QUERY(daily_fee_collection!A:D,"select D where B="&A:A&"",-1))))) I am trying to cross-check for payments made in ...
25 views

### How to summary the multiple entry into a single sheet by name and by date?

Names are in rows and Dates are in columns. Sheet 1: Name 1-Jun-18 2-Jun-18 3-Jun-18....... KB CL KB EL AJ SL SD SL SD CL I need ...
591 views

### Aggregate results by week and month for charts

Is there a way to convert daily values into weekly averages for charting? Sample source sheet --------------------------------------------------- | Data 1 | 1/1/2018 | 1/2/2018 | 1/3/2018 | etc......
11 views

### Survey Data & Google Sheets: Excluding Missing Values?

I am using survey data in Google Sheets with rows for "before" and "after" responses, with the same survey administered to the same subjects at two different time periods. With each question, a number ...
28 views

### why does this array formula on google sheets display no match when i add a new row?

hi and thank you for reading my post. please help me with my array formula. the goal of the formula is to display the status on the column of my work sheet (queue, working, done and blank). it works ...
10 views

### Arrayformula with iferror and vlookup does not update automatically

My spreadsheet is for inventory checking. I use several of arrayformula ,iferror and vlookup. The problem is I just noticed that sometimes the formula does not update. It might because I duplicated ...
38 views

### Check for regexmatch in multiple ranges in Google Sheets

I have a sheet that auto-populates with a sub-set of articles (each a row with a column representing various information such as title, year of publication, author name, etc...) on the basis of a set ...
25 views

### How can I change the formula of one cell based on the value in a different cell?

COLUMN D: Dropdown list of values COLUMN E: IF function returning different values depending on COLUMN D contents I have the functionality described above working properly. My problem is that I want ...
11 views

### Output data if value is less than today's date

I have a spreadsheet. I want to create a formula that checks the date of one cell and if the value is lower than today's date, it to output the word required into another cell. How would I do this?
12 views

### Issue with finding the averages of multiple rows at once

I am currently tasked with hundreds of rows of data for a project. I need to take the average of each row, but there are way too many rows for me to do by each individual row. Is there any way to take ...
81 views

### Google Finance Historical Data with Multiple Attributes?

=GOOGLEFINANCE("NSE:"&A2, "all",(TODAY()-(\$N\$2)),TODAY(),"DAILY") The above code returns OHLC data for specified days with the date column. What I want are only high, low and close columns. =...
18 views

### How to get cell's parent ID?

Firstly thank you for helping me out. I'm in the construction industry and don't have any clue how to best do this - not my forte! I'm trying to bulk add a bunch of building product categories to my ...
13 views

### Adding mm:ss times when expressed as TEXT

I have a series of numbers that, for a reason out of my control I can not change, are expressed as plain text strings on google sheets. The numbers are supposed to be minute second. mm:ss. For the ...
43 views

### How to make sequential list of months?

I want to fill column names with a sequential list of the last day of each month. For example, 3/31/2018, 4/30/2018, 5/31/2018, etc. Is there a formula that can do this? If cell B1 = 3/31/2018, I ...
25 views

### Concatenate - Show blank cell result if a cell is empty

Could someone help me? I am trying to create a social media keyphrase strategy using keywords. I would like to know if Google Sheets could return a blank cell if one of the cells in the concatenate ...
38 views

### How can I track the time it takes for someone to fill out a Google form?

I am running a study for my thesis, and would very much like to track the time it takes to complete a Google form to compare against how long it takes to fill.
24 views

### Insert a weekday in Google Forms' date selector

We have created a Google form that works perfectly, there is only one issue, and that's the DATE selector, it sends the DATE, but we would like to receive the DATE and the WEEKDAY in the form we ...
416 views

### How can I create a recursive formula in Google Sheets?

I'm making a task tracker in Google Sheets and am trying to implement subtasks. I am trying to create a formula that checks a row's "Parent Task" column, and if there is a parent task, indents the ...
17 views

### Autopopulate a RANGE of Cells from one sheet (tab) to another in a single workbook

All I want to do is have cells in one sheet (tab) in a workbook auto-populate with data and formatting of a different sheet (tab) in the same workbook. I know how to do this one cell at a time, BUT is ...
167 views

### How to pull last value in a Google Sheets column and populate it across another worksheet column?

This is my first post here- I hope this is the right place for it, apologies in advance if it isn't. Context: I have a password protected presentation that I need to give users access to, but only ...
75 views

### How do I get the count number of weekdays in a datetime column in Google Sheets?

I have a timestamp in mm/dd/yy hh:mm:ss format from January to March. How do I count "how many days are Monday" in the range of A1:A199? (Or How Many are Tuesday, Wednesday, Saturday? This gives me a ...
266 views

### How to scan rows for match with ArrayFormula?

Trying to search a row for a given value, but want to make an ARRAYFORMULA so that every time a new row is added the search will be performed on that row. Currently using =IF(COUNTIF(\$A2:\$EI2,"="&...
37 views

### How to combine dynamic columns in Google Sheets while retaining data in other rows?

I have a Google Form that asks people to fill out their name, gender, and then their size preference for shirts and pants. The form automatically outputs the 2 sizes in separate columns. I want to ...
202 views

### How to concatenate cells using ARRAYFORMULA across rows and columns?

I have a document that's a recurring CSV import/export. I'm trying to concatenate images separated by ", " and using ARRAYFORMULA to make it all the way down. The problem is, I don't know how many ...
43 views

### Populating alternatives in question 2 based on answers in question 1

I have ten people who sell something to companies, and I want to track what they do IN GOOGLE FORMS. Every agent has his own 10 companies that he contacts. So, in Question 1, I will have a drop-down ...
41 views

### Google Sheets' Query or Filter solution

I have a simple table with columns: A (Donor ID) B (Donation date) C (Donation Amount) I need to extract the following report: For each donor ID most recent donation date and amount donated ...
17 views

### LOOKUP for data from other sheet

So I have a drop-down menu in order sheet and I have an info sheet. In the info sheet, I have 2 columns, column A has the customer name, column B has the customer e-mail. So what I'd like to do is, ...
32 views

### How to join VLOOKUP results of multiple sheets into one cell

I am trying to use TEXTJOIN across multiple sheets, IF a cell is is the same I would like it to return the value from another cell in all sheets that are associated with it. In this example, I am ...
34 views

### How to create a user defined function that maps one value to another in Google Sheets

I would like to create a mapping function that takes as an input 3 arrays of the same length and a double between 0 and 1. Array #1: Each element is the minimum value of the nth bin. Array #2: Each ...
1k views

### Dynamic updating referenced cell from another workbook after adding/deleting rows using IMPORTRANGE - use ARRAY or VLOOKUP?

I add/deleted/move rows frequently in my workbooks. When I add/remove rows from Spreadsheet#1 ("S1"), I need the referenced cell in Spreadsheet#2 ("S2") to dynamically update so it still references ...
11k views

### Google Sheets formula for “if contains” from range

I try to figure out how to get a value based on "if some word is in a range". So formulas from (Google Sheets formula for "if contains") works: =IF(ISTEXT(REGEXEXTRACT( A1, "sites")), 1,0) ...
203 views

### Is it possibile to concatenate with bidimensional arrayformula?

I have a table like this: +----------+----------+----------+----------+ | Photo1-1 | Photo1-2 | Photo1-3 | | +----------+----------+----------+----------+ | Photo2-1 | Photo2-2 | | ...
467 views

### Range notation and current row

I wonder how does work the following and is it documented somewhere? I have the sheet: A B ----------------- 1 2 3 =\$A:\$A 4 5 =\$A:\$A For column B (row 3 and 5) I see ...
73 views

### Function always parses column in first row but would like it to repeat

I am trying to make a function that repeats in each row of a column that fetches the URL in the first column, does an xpath query and gets an image. Currently its all working other than the fact it ...
31 views

### How to Iterate between colunns and rows?

I am running the following forumula: =IF(INDEX(C\$2:\$2, 1, ROW(1:1)),CONCATENATE(B\$2,"-",INDEX(C\$1:\$1, 1, ROW(1:1)))) On the following table Basically, If a value is present in column C2, I am ...
34 views

### Google Sheets' Duration Input issue

We have been using Apple Numbers which seems to have this duration thing figured out a little better than Google, (either that or I'm missing something). In Apple Numbers, if I type into a cell with a ...
12 views

### How to filter after parsing csv values contained in Google Sheets cells

I would like to be able to match all cells in column A (titles) that have one of the comma separated values in column E (Key Search Terms). This will involve at least two steps. 1) Parse each of the ...
18 views

### Sequential numbers via formula - Google sheets - IF & MAX

I use this formula to find highest value in column A of other sheet, and then add 1 to it to get new unique number for a record ID, and I use... =if(B3<>"",max(Backend!A3:A)+1,) with this ...
11 views

### IF formula to subtract from amount in another cell

I am setting up a Google Sheets spreadsheet with a few columns as follows:- Pref|Room|Item|Price|Got I have a formula to add up the total of all the cells based on the room and if they are a ...
17 views

### Google Sheets Form Response Split Multiple Responses

I've created a form for my staff to enter metrics about their job so I can track how new operations affect their output. For the ease of my staff, I'm hoping that they can submit multiple responses ...
3 views

### I want to use ArrayFormula to sum and automatically expand the results vertically with start and date range

I want to use ArrayFormula to sum and automatically expand the results vertically with start and date range. Currently I have this so far: I want to use only one formula to sum and automatically ...
18 views

### How can I extract specific information from the cells

How can I extract just the: Brand, Showroom Only Product, etc.
17 views

### Having trouble outputting GOOGLEFINANCE data

My wish is to create a table in Google sheets looking something like this: price day A B C D ... Z 1997-01-02 132.54 . . . . 1997-01-...
24 views

### Calculate hours worked but not limited to a single day

I'm trying to automate getting the number of hours worked for a session, not a day. My raw data is the following: Here is a sample spreadsheet for the data I was able to create a formatted version ...
12 views

### How to automatically add a row above a particular cell?

I'm sending some data from Zapier to Google Sheets and every time Zapier creates a new row, all formulas from that row are gone. I'm looking for a script that would allow the following solution: I ...
16 views

### Comparing data changes over time?

Let’s say I’ve collected data on a scale of bins from 1-6 at two different time periods (T1 & T2), organized as two columns in Google Sheets: T1 T2 1 2 1 5 2 3 5 ...