Questions tagged [google-sheets-arrayformula]
Use for questions about the use of the Google Sheets built-in function ARRAYFORMULA.
540
questions
2
votes
1answer
324 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
170 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
27 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
2answers
632 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
298 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
105 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 ...
2
votes
1answer
344 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
98 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) ...
2
votes
1answer
135 views
How to sum the values of same vlookup results?
I have this formula it gets the single data from another file from a sheet at column 7.
=ARRAYFORMULA(
if(
len($A2:$A);
iferror(
vlookup(
...
1
vote
1answer
1k views
How to automatically fill missing data in Google Spreadsheet
I'm searching for the exact same thing as Google Spreadsheet: How to fill in missing values (inbetween filled values) automatically
I made a spreadsheet for tracking weight, where each row is one day,...
2
votes
1answer
160 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
votes
0answers
759 views
Multiple filter Array formula in google sheets
I am trying to get my array formula to be able to filter on multiple drop down menus. I have 2 separate formulas that work but I cannot combine them. Is there a way to combine them to make the Array ...
0
votes
1answer
129 views
Sumproduct within Arrayformula [duplicate]
This is my formula in cell D2:
=SUMPRODUCT(B:B=B2;F:F>F2)+1
The values in column are all of the same length, and are continually updated by contributions from google forms. Therefore a condition ...
1
vote
2answers
43 views
Use Google Sheets to create column C by deleting any phrases that contain word found in column B in column A
I have a list in column A and I want to remove any cells that contain any word.
0
votes
2answers
164 views
Extracting last match from a string with REGEXEXTRACT
I've created a simple text filter:
=IFERROR(REGEXEXTRACT(A1;"- May 2019| - Jun 2019|- Jul 2019| - Aug 2019|- Sep 2019"); FALSE)
It should extract the month and year for an event from the cell A1. A1 ...
0
votes
1answer
68 views
How to find the last value in a column when the column is populated from an ARRAYFORMULA
Some columns in my sheet are filled manually, but then two columns are created dynamically from an ARRAYFORMULA (i.e. there is nothing in the column if the manually populated cells are empty, but once ...
0
votes
1answer
42 views
Merging cells based on same ID but divided into categories (columns)
I receive a pretty extent list of material that has been delivered and every single product is in a separate row. That means for one order I have easily a lot over 15 rows and I need to merge the ...
0
votes
2answers
32 views
How can I make sure an equation doesn't give an answer if more than one cell is blank?
I have half the equation working:
=IF(AND(IF(NOT(ISBLANK(AG22)),TRUE,FALSE),AJ22<AG22),"1","0")
So it looks like this:
What I WANT it to do, is to only prompt an increase or decrease response if ...
0
votes
2answers
36 views
How do I “bin” results in Google Sheets?
I am trying to bin my results so my groupings have lower granularity - going from a 1-10 scale to a 1-5 scale.
For example, any result 1 or 2 would be renamed to 1 in the next column, any result 3 or ...
0
votes
2answers
96 views
Using TODAY function with QUERY
I am using this formula (see below) to try and retrieve dates automatically between the current date and the next. However, I keep getting the error, "QUERY completed with an empty output."
...
0
votes
1answer
223 views
Pull unique values from other sheets as new sheets keep getting added
I'm trying to get a unique list of values in a master sheet based on a list of values that appear in column A of other sheets. The tricky part is that every time a new column is added in the master ...
1
vote
2answers
30 views
Searching a list and replacing value if any item matches any item in the other list in Google Sheets
I have a list of usernames on two different sheets. I need to scan the first list of usernames to see if they appear in the second, and if they do, replace them with a predefined username. I can't ...
-1
votes
2answers
16 views
Value of Cell A1 could point to values of different cells
I am having difficulty with the following. Cell A1 could have values from 1 to 50. I have a listing on a separate table of different values that 1 to 50 represents. (I.E. 1 equals 1.68) I would like ...
1
vote
2answers
247 views
Remove Count column by repeating rows in Google Sheets
I have a worksheet, 'wi', in a Google Sheet similar to the following example but with an undetermined number of rows:
Count | Entry | Description
-------|-------|-----------------
2 | ...
0
votes
2answers
146 views
Showing top 5 most frequent values and counts of each from a data range
I have a Google sheet with text strings listed down columns D through G. I want to, on another sheet, display a list of the top, say, 5 most frequent unique values from the first sheet in descending ...
-1
votes
3answers
24 views
How do I sort data by size and not in ABC order
I am new to Google Sheets and have only used Excel a little, so Google Sheets is a bit different. I want to sort our band uniforms in size order from smallest to largest but Google Sheets only sorts ...
0
votes
2answers
61 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
2answers
70 views
IF and IFS not working properly
I am trying to locate data present in different sheets. I have tried using IF and IFS.
Below are the formulas I have used. For IF, I get NOT IN USE even when I know there should be one of the sheets ...
0
votes
1answer
51 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
31 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
526 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
33 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
30 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
19 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
51 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
73 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
1k 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
1answer
25 views
How can I extract specific information from the cells?
How can I extract just the: Brand, Showroom Only Product, etc.
0
votes
1answer
183 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
54 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
32 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
205 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
22 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 ...
-1
votes
1answer
416 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
38 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
42 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
33 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
16 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
74 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,...
