Questions tagged [google-sheets-arrayformula]
Use for questions about the use of the Google Sheets built-in function ARRAYFORMULA.
158
questions with no upvoted or accepted answers
4
votes
2answers
899 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 &...
2
votes
0answers
96 views
Create a pivot table with a column for each assignee, containing rows of task descriptions
I have a two-column table with the first column being "task description" and second column being "assignee". This is something I'm unable to influence.
Crude representation:
I'd like to be able to ...
2
votes
2answers
47 views
Extracting range from of an array
From the following single cell array:
0, 1, 1, 2, 3, 5, 8, 13, 21, 34
I'd like to extract 3,5,8,13,21 into a single cell by specifying index 4 : index 8 of the split of that cell.
The Challenge is ...
2
votes
1answer
53 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 ...
2
votes
2answers
3k 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 ...
2
votes
3answers
219 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,", ")...
2
votes
1answer
48 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 ...
1
vote
1answer
43 views
Issue trying to sum different columns
I am trying to sum 3 different columns and I'm not being successful.
My function is: =ARRAYFORMULA(SUMIF(MONTH(D:D), 2, A:C))
Where D is a column with dates, and A,B and C columns with currency (A-...
1
vote
2answers
34 views
Array formula with index match to return array of matches
I've been working at this for a bit. I have tried many times different formula combos, using VLOOKUP to using INDEX & MATCH, even with an ARRAYFORMULA.
What I'm attempting to do is match a ...
1
vote
1answer
41 views
Help Sorting Data
I have a spreadsheet that I'm using to keep track of stats for players in a league. This is the link:
https://docs.google.com/spreadsheets/d/193GxNzaX4-bGrNB8eT4vhqAOKhUrChRDgq-HYVU_Txk/edit#gid=...
1
vote
0answers
16 views
Google Sheets Area Graph(s)
I need (want) to create a Google Sheets Area Graph (Chart).
The Graph/Chart will represent "Date"s.
I need the Xaxis to be the dates (as days) between these start and end dates:
01/27/2020, 4/25/2020 ...
1
vote
0answers
17 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;'...
1
vote
1answer
27 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
471 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 ...
1
vote
0answers
23 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 ...
1
vote
0answers
44 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 ...
1
vote
0answers
62 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 ...
1
vote
1answer
891 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 ...
1
vote
1answer
53 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 ...
1
vote
1answer
37 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?
1
vote
1answer
16 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 ...
1
vote
1answer
506 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.
=...
1
vote
1answer
28 views
How to get a cell's parent ID in Google Sheets?
I'm trying to bulk add a bunch of building product categories to my site's MySQL database. There are about 650 entries and about 4 levels in the hierarchy. For it to work, each entry needs an ID, and ...
1
vote
1answer
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 ...
1
vote
2answers
57 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 ...
1
vote
1answer
75 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 ...
1
vote
1answer
461 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.
1
vote
1answer
86 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 ...
1
vote
2answers
2k 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 ...
1
vote
1answer
56 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 ...
1
vote
2answers
158 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 ...
1
vote
1answer
234 views
Arrayformula for running MAX of separate column
I'm trying to use ARRAYFORMULA in Google Sheets to get the max figures from a separate column only up to the row being calculated each time but can't for the life of me figure out how. It sounds more ...
1
vote
3answers
671 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,"="&...
1
vote
1answer
160 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 ...
1
vote
1answer
421 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 ...
1
vote
1answer
124 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 ...
1
vote
1answer
22 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, ...
1
vote
1answer
59 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 ...
1
vote
0answers
35 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 ...
1
vote
1answer
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 ...
1
vote
2answers
21k 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)
...
1
vote
2answers
241 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 | | ...
1
vote
1answer
689 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 ...
1
vote
0answers
76 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 ...
1
vote
1answer
38 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 ...
1
vote
1answer
114 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 ...
0
votes
1answer
11 views
How to clear a cell if there is no data in it
How to clear a cell if there is no data in it?
=ArrayFormula(REPLACE(IMPORTRANGE("1I1MK51uYT_WJDh0fiiHxLjnYMoQOVoZi41XnHM", "SetstudentMatrik!C2:C"),1,6,"******"))
0
votes
0answers
32 views
How can I SUMIF a row, but have the formula automatically applied to all new rows below it in Google Sheets?
I've made an inventory tracking spreadsheet that gets raw data from Google Forms. Forms inputs data into Sheets in a less than ideal way, but there's nothing I can do about that, so I'm stuck post-...
0
votes
2answers
46 views
Make a query argument work with arrayformula in Google Sheets
Can you help please?
I have worked out how to get the result I need, but how do I make them work with arrayformula? Here is my google sheet
This works for me in Y2:
=COUNT(QUERY('Feed from Register'!...
0
votes
0answers
47 views
Splitting checkbox response into multiple rows on another sheet
I am trying to take a Google Form response page, and change the way the data is displayed. Currently, I have the raw data on the first sheet, and I'm trying to format a second sheet that will split ...
