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
1answer
23 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
19 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
15 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
14 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
21 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
16 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
39 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
15 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
27 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
20 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
27 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
34 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
37 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
27 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
41 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
40 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
31 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
53 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
27 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
60 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
28 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
20 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
27 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
91 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
157 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
23 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
43 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
63 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
93 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
92 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
62 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
38 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
166 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
26 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 ...
0
votes
0answers
71 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
56 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
22 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
67 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
31 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
26 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
30 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
27 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
36 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
24 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
25 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 ...