Questions tagged [google-sheets-arrayformula]
Use for questions about the use of the Google Sheets built-in function ARRAYFORMULA.
414
questions
1
vote
0answers
4 views
Multiple IF-statements in a single cell - checking long list of results
I have a Google Sheets where I enter all the results of my beer pong matches with friends.
I'd like to see my amount of wins/losses with certain friends. This can be done with
=IF(AND(B2="Won",E2="...
0
votes
1answer
9 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 ...
0
votes
1answer
17 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 ...
1
vote
1answer
21 views
Extracting specific phrases from a cell and echoing them in another
Longtime learner, first time poster here!
I was wondering if anyone knew how to extract specific data points (phrases) from a Google Sheets cell and echo them in another cell.
For instance, in my ...
1
vote
1answer
11 views
How to generate a range of serial numbers by formula?
I want to produce an array of numbers from 1 to n, where n is a value in another cell. So if the cell value is 10, I want to generate an array of the numbers 1 to 10.
I found this resource which ...
1
vote
1answer
25 views
Conditional row sum
I'd like to sum a column of cells if the corresponding cell on the next column contains a string of any sort. I've tried using SUMIF but I can't figure out how to combine it with ISBLANK or ISTEXT.
...
1
vote
0answers
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 ...
0
votes
0answers
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 ...
1
vote
0answers
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 ...
1
vote
0answers
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 ...
1
vote
1answer
37 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 ...
0
votes
0answers
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 ...
0
votes
0answers
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 ...
0
votes
1answer
18 views
How can I extract specific information from the cells
How can I extract just the: Brand, Showroom Only Product, etc.
0
votes
1answer
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-...
1
vote
1answer
46 views
Get last value entered past a specific row
I have been on this a couple of days and I cannot get my head around this ... I use a spreadsheet to track the number of miles traveled by drivers and I want to work out how many miles a driver has ...
1
vote
1answer
10 views
Conditional Output on cell based on 2 different “IF(REGEXMATCH” functions
First of all sorry if I am not clear. it is my first question.
I am trying to make a cell give an output with IF(REGEXMATCH on the cell A1 for example
=IF(RegExMatch(A2,"YES"),"Good"
I also need ...
1
vote
2answers
20 views
Formula for getting the Left most cell value from a 'Range of cells'
Need your support in finding a solution for an issue which I am facing now.
Formula for Column A... Data Range from B to D
Have values in a range of spreadsheet cells (From column B to D)
Value ...
1
vote
1answer
9 views
Google Sheets forumla help
With my sheet, I need it so that if the job is any type of "Repair" then it minuses 1000 from the business profit but if it's anything else it doesn't minus the 1000 from the business profit. I have ...
0
votes
1answer
19 views
How to merge data from multiple Google Sheets with formula?
I have 10 columns in 10 Google sheets and in all sheets are the data labeled like
Month | Total number of winter/summer schools attended | Sr. No. | Title | Date | Venue | Organisation | Number of ...
0
votes
1answer
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 ...
1
vote
1answer
28 views
How to Update a exactly sheet tab row with a value from a imported data in another tab in Google Sheets?
I have this tab in Google spreadsheet
[https://docs.google.com/spreadsheets/d/14ZBff8R6brLYWtfv70V-tyG0HE-sX_ejySvplQqZftM/edit#gid=1450402909]
Sheet All Tasks, Candice Tasks, Others People Tasks
...
1
vote
1answer
23 views
Returning max value based on two conditions
I've been reading through a lot of other solutions to similar problems but am not able to resolve my issues.
I have a Google Sheet with two sheets I am working with:
Sheet 1
A list of contacts (...
1
vote
1answer
12 views
Rank days of the month that occur most
I would like to know which days of the month customers have historically paid on the most. I would like to create a report that has days 1,2,3,4...31 all with a count next to each day for how many ...
2
votes
1answer
16 views
ArrayFormula to create a table of data from 1 column
I have the below formula and want to turn it into an array.
=INDEX($A:$A,ROW(A1)*5-5+COLUMN(A1))
I have tried it with the below attempt but only get 1 single cell result.
=arrayformula(INDEX($A:$A,...
0
votes
1answer
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 ...
0
votes
1answer
27 views
Is there any way to dynamically modify dates based on number of days something would take?
Imagine you're creating a Google Sheet to track all of your tasks, how long they take, and their deadlines:
Task |Number of days| Start Date | Date Done
Buy plane tickets | ...
1
vote
1answer
21 views
Checking whether two columns contain the same string of numbers
Some time ago I have put together a perfectly working formula but now it's not working and I can't tell why (I may have changed something and don't remember that).
Column B contains data that I need ...
0
votes
1answer
15 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 ...
1
vote
1answer
19 views
GOOGLEFINANCE in Google Sheets to get high & low and timestamp of high/low
I'm using Google Finance calls in Google Sheets for some calculations.
I've got 2 formulas, one to find the low price, and one to find the high price from a certain date:
=MIN(INDEX(GOOGLEFINANCE("...
1
vote
1answer
20 views
ImportXML With Line Breaks Google Sheets
I am using ImportXML to get some data from a website which looks like the following:
=ImportXML("https://www.papercall.io/events?cfps-scope=&keywords=uk","//div[@class='panel panel-default']")
...
1
vote
1answer
19 views
Sum a column at the bottom of it
In Google Sheets, how do I get - at the bottom-most cell of a column - the sum of each cell (except for row 1, the header) above in that column? Keep in mind, I may add rows in the future.
1
vote
1answer
25 views
Query sum for unique pairs of rows
I have a spreadsheet which logs the duration an employee spends in a city. An employee can visit a city multiple times like Peter in this example.
City Person Days
London Tom ...
0
votes
1answer
13 views
Countifs in between ranges
I'm struggling to pull below conditions together. I'd like to have a formula that will count values between 1 and 5 only when cell U13 contains the text 1-5.
=COUNTIFS(U13,"1-5")
=COUNTIFS(K2:K22,"&...
0
votes
1answer
25 views
How to partial extract from 3 columns with multiple levels of rules?
The formula someone on this forum provided worked well, but I have run into a problem, which I am struggling to get around.
Google sheets Link
The formula in H2 looks between 00 & 02 and ...
1
vote
1answer
40 views
ArrayFormula to extract part of the string
I have come across the below formula, and I am finding it quite useful, I want to turn it into an array shortly, but before I do, I was wondering if there was an easier way to do this?
=if(len(LEFT(...
1
vote
2answers
44 views
Combining IMPORTRANGE, SUMIFS, QUERY with cells that contain numbers and text
I'm having an issue combining SUMIFS with ImportRange in Google Sheets. I found answers suggesting to nest it with QUERY. However, the issue is that one of my criteria is non-numerical.
Formula in ...
0
votes
1answer
10 views
Process visualization based on data
I am trying to use a Google spreadsheet to visualize a customer journey.
I am trying to create the following graphic:
I have created the following example spreadsheet:
https://docs.google.com/...
1
vote
1answer
19 views
Array formula for adding letters after duplicate numbers in order
The below formula adds a letter after a sequential number, A, B, C, D, etc for the same number until it finds a new number, then it starts again from A.
I need help on turning this into an array ...
1
vote
1answer
36 views
Escape single quote in cell content in a QUERY
My Problem
I have a Google Spreadsheet with two tabs - one containing regexes and values:
And the other containing phrases which matches regex in the first tab, and a QUERY Formula that picks the ...
0
votes
1answer
16 views
Assigning a value to a name from a list
I have items that I'm selling, and I've named these codes with the item I'm selling. I want to give these codes the price they should display. I've already got a formula to show the product of the ...
0
votes
1answer
14 views
Can I populate Google Sheets cells adjacent to a target cell?
I have a maintenance tracker in Google Sheets for lighting fixtures. One column is a type of fixture "B", the column adjacent is the problem "C". I'd like to find specific fixtures by name and copy ...
0
votes
1answer
18 views
Conditional formatting with multiple names in list
Col A2:C15 contain a list of names. I need to enter those names in various combinations into one of 15 other cells on formatted like:
H3 = John, Steve, Bob, Dan
Is there a way to use conditional ...
1
vote
1answer
11 views
How to create new cell from keyword of another cell?
I have a column that has job listings with extra info, so I only want to grab the job position and place it into a new cell.
A1: Seeking Assistant to the marketing division...
B1: Assistant
(...
1
vote
1answer
26 views
How to extract data from a table dynamically?
I have the following table
And I would like a formula that would produce the following result
I would like to iterate through the table going from left to right top to bottom and if there is a ...
1
vote
1answer
27 views
How to search value in another column?
Using Google Sheets, I am trying to two compare two columns on Google Sheets, like the one below:
Basically, I want to compare column A and B, basically, if a cell in column A is inside a cell in ...
1
vote
1answer
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 ...
-1
votes
1answer
18 views
Google Sheets images
When I insert an image in a cell in Google Sheets from my camera how is that image stored in the cell? I know it creates an image in my phone in a folder called images, but if I want to link that ...
1
vote
1answer
22 views
Delete Google Sheet rows matching multiple criteria with custom formula
I have a Google sheet where I am trying to remove multiple rows matching any one of a vector of values using (Filter by condition -> Custom formula).
For this example, let's say I want to remove ...
1
vote
1answer
18 views
How can I return a string instead of a number when the number is 0
I have a formula that returns average lbs, waist and body fat % for the weeks I've been dieting. The problem is I didn't measure body fat % until a while after I started so some averages are 0. I'd ...
