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
29 views

Google Sheets Formula - Trying to Sum, based on multiple cell criteria

I am looking for help with Google Sheets formula. The formula I need is - If A:A=LG and F:F=Indexed then sum the total of P:P for all of those instances. If A:A=PG and F:F=Indexed then sum the ...
1
vote
2answers
88 views

COUNTIF with relative cell range in condition

I want a Google sheets formula along the lines of =countif(a4:a150;and(sum(e4:j4)<132;sum(e4:k4)>=132)) I.e., for each of the rows 4-150 (column A is not relevant), if the sum of columns E-J ...
1
vote
1answer
120 views

Google Sheets - Identify if one of many matches any in an array

I am trying to determine if an array of values in Google Sheets has any matches within another array of values. The data set is constantly expanding, so I want to use an Arrayformula to handle it. ...
1
vote
3answers
2k views

ArrayFormula combined with ISBLANK

Kind of new here. Tried to look for a similar answer but not quite getting it to work. I made a formula - it worked. I turned it into an array formula - it worked. But it was returning 0's in the ...
1
vote
1answer
2k views

Append the cell content as a URL parameter and create a link

Here's a sample form: https://docs.google.com/forms/d/e/1FAIpQLSfiPSHacKKNEfe-Jq82DBScbCBRkwtm8rq2Jq7y-ceLgGIC3Q/viewform And here's the linked spreadsheet: https://docs.google.com/spreadsheets/d/...
2
votes
1answer
78 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,...
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;'...
0
votes
0answers
23 views

Formula is not working when adding ArrayFormula

I am making a 3d Print Log so I can keep track of my prints, their Material cost. EDIT: Seems part of my post got deleted when I submitted. But your suggestion worked. Thanks so much! =ARRAYFORMULA(...
1
vote
1answer
78 views

Query/Filter function finding the closest date that is less than the order date for an array of dates using a given variable

Overview I need to get the cost of something by comparing item dates against an order date for one or more of the same item with different item dates, and making sure to select the item date that is ...
0
votes
1answer
557 views

Count consecutive columns with the same values?

Before I start, here is a link to the sheet example: spreadsheet sample Essentially what I want to do is count the number of times the value in C5 occurs consecutively in its first occurrence. In ...
1
vote
1answer
31 views

Formula Error: =IF( SEARCH() ); Visual Reference to Question

I know I'm very close with my formula: =IF(SEARCH("Wednesday", (A3:A)), "workday", 0), But I must be missing something here...: I can't get this formula to display the value "-" when I run it on a ...
1
vote
1answer
26 views

Search for missing data starting with certain text

Hello I need to compare two columns and output all missing data into a third column. The problem is that I need to also filter the first column for only certain cells that start with two letters ...
0
votes
1answer
86 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
1k views

Concatenate using an array formula to result in last name.firstname

We have a form that asks for the students first name and last name. I am trying to using concatenate and an array formula to display "Lastname.Firstname". I used: =ARRAYFORMULA(CONCATENATE($B$52:$B,...
0
votes
1answer
36 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
21 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
29 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
41 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
510 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 ...
1
vote
1answer
312 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 ...
-1
votes
1answer
3k views

Convert phone number format from (404)-313-2974 to 404-313-2974

I have a list of phone numbers entered into Google spreadsheet in this format: (404) 313-2974 How can I convert this entered cell into 404-313-2974?
0
votes
1answer
139 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
34 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
1answer
27 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
1answer
40 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 ...
1
vote
1answer
46 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
votes
1answer
199 views

Google Sheet Query returns first 3 rows in one row

I have made a simple query that combines data from 4 separate sheets: =query({'1.MA'!A13:O200;'2.PF'!A13:O200;'3.BM'!A13:O200;'4.C'!A13:O200},"Select * where Col2 is not null ") The objective is to ...
1
vote
2answers
63 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
59 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....
2
votes
3answers
176 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 ...
1
vote
1answer
34 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
1answer
315 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
34 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 ...
0
votes
1answer
259 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 ...
2
votes
1answer
46 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 ...
1
vote
2answers
2k views

How can I set the increment value for Google Spreadsheets to auto-increment?

OP Edit: I think I want Sheets to behave in a way it's not intended. I expected Sheets to recognize the incremented value in my formula the way I believe it recognizes the increment value in a ...
-1
votes
1answer
16 views

Multiplying several numbers within a text string, is this possible?

I have cell A1 which is a text string: 100 blue cars, 100 red cars, 100 yellow cars In cell B1 I have a number which is a multiplier say 2. Is it possible to change the numbers within cell A1 ...
1
vote
2answers
591 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 ...
1
vote
2answers
54 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.
1
vote
1answer
105 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
387 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
2k 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......
-1
votes
1answer
90 views

How to paste a comma separated string into one column in Google Sheets?

For example, if I have (bob, china, bing, bong). I want to put bob on top of china on top of bing on top of bong.
0
votes
1answer
1k views

How can I use ARRAYFORMULA (or something similar) with GOOGLETRANSLATE?

I'm learning a new language and I recently discovered Google Sheets' GOOGLETRANSLATE function. I thought it'd be neat to have a personal dictionary of translations which I can even annotate. So I ...
2
votes
1answer
172 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?
1
vote
2answers
7k views

Google Sheets Arrayformula on both horizontal & vertical cells?

In a sheet, I make calculations horizontally, however I can't build the arrayformula to expand calculation vertically on each row. See the following sheet: https://docs.google.com/spreadsheets/d/...
1
vote
1answer
28 views

Autofill a cell when dates are entered in 3 other cells

I'm trying to create a way to keep track of our jobs by creating a Google spreadsheet. We can only order our final product when the digital and hard copy proof is approved and we have an order from ...
0
votes
1answer
20 views

How can I have a 2nd function start in the cell after the output of the 1st function?

So the problem I am trying to solve is where I have Google finance fetching the price of a stock and outputting it, but the length of cells of its price history is increased by 1 each day. In the ...
1
vote
1answer
1k views

How to use ARRAYFORMULA and UNIQUE together in Google Sheets

How can I expand the UNIQUE function using ARRAYFORMULA? Example: Here is my data set, also can be found here. I want to see the unique values of each row in next columns. CL1 | CL2 | CL3 | CL4 JAN |...

1
3 4
5
6 7
11