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
287 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-...
0
votes
1answer
46 views

SUM across other worksheets (without hardcoding their names)

I've got a spreadsheet for my organisation's management accounts. Each project has a worksheet, setting out income and expenditure by financial year. This is aggregated together in a few sheets ...
0
votes
1answer
23 views

Cross Comparing values between two columns for voting purposes

The current content of our Google sheet, is formatted as such: - PERSON 1 | PERSON 2 | YES - PERSON 1 | PERSON 3 | YES - PERSON 2 | PERSON 1 | YES - PERSON 2 | PERSON 3 | YES - PERSON 3 | PERSON 1 | ...
7
votes
2answers
1k views

How to SUM a row of numbers after extracting numbers with REGEX?

I have columns of data that are of the form: A1: "IDjohn / 35.00 / california" A2: "IDmike / 25.00 / oregon" A3: "IDrebecca / 40.00 / ohio" B1: "IDchang / 20.00 / washington" B2: "IDwill / 25.00 / ...
0
votes
1answer
47 views

Auto sorting / copying data based on inputted data?

Sorry if this is a bit confusing or if I’m not explaining this well enough. Using my provided picture as an example how would one auto copy data that gets inputted into that table based on estimated ...
0
votes
1answer
20 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
2answers
1k views

How do I keep leading zeros in Google Sheets after split?

This is my data: 43558#DEYINGCHEN20190403#097855111593#Logitech MX Master Wireless Mouse (910-004337) Then I used split(#). Result: The result I want is:09785111593 This cell I have formatted into ...
1
vote
1answer
2k 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
1answer
48 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 ...
0
votes
1answer
520 views

ImportRange not pulling in Dates from first sheet in correct format

I'm trying to pull in a date cell from one sheet to another with an importrange function: =iferror(query(IMPORTRANGE("https://docs.google.com/spreadsheets/d/1UClhpev01z-...
1
vote
1answer
50 views

How to filter Queried Data in an Array

Sample Spreadsheet: https://docs.google.com/spreadsheets/d/16p4M5UBODyBhmWFXYrA0FdLsA1FmwSmbrF12VUHlGaw/edit?usp=sharing I have this formula which works great: =ARRAYFORMULA(QUERY({Projects!A:C, (...
0
votes
1answer
677 views

Arrayformula and Sum queries. Possible alternatives

I am using a sheet to list and summarise teachers absences and substitutions. Here's the worksheet Basically in the sheet "Substitutions" all absences and substituions are stored record by record: ...
1
vote
1answer
556 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 ...
0
votes
1answer
689 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
31 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 ...
0
votes
2answers
230 views

How to export CSV without commas in unpopulated cells?

I have a form converter which simply rearranges columns. I use a script to paste Values only into another sheet for export and uploading onto another website. However, when I go to export, it seems ...
0
votes
1answer
16 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
16 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 ...
2
votes
2answers
66 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 ...
0
votes
1answer
39 views

Copy data based on date to another cell but other column date is not exact match

I want to match the dates in Column A to the Closest date in Column E (if a date in column A doesn't match to dates in Column E then I want to return the next available date in Column E and I've done ...
0
votes
1answer
29 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
20 views

Is there away on google sheets to paste text and automaticly create new rows with every line break or etc?

Is there away on google sheets to paste text and automaticly create new rows with every line break or ect? I will paste a lot of text by this format in the example below (700 lines in total): ...
0
votes
1answer
66 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
50 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
2answers
170 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 ...
0
votes
1answer
22 views

How do I include blank cells when using an Array Formula?

I have a Google form that I'm using to collect info for user account creations. I wanted to make it so that the person filling out could just let me know about multiple users at an office in one entry ...
0
votes
1answer
192 views

How do I calculate weighted rates in Google Sheets?

I would like to calculate the cost on $D "Rent" with a weighted cost when the respective cell in $B="PB". The calculation would be based on the formula 7x+1.3x=1252 where 7 is dependent on COUNTIF(A2:...
2
votes
1answer
70 views

How do I extract specific value from a single cell on Google Sheets

I'd like to get respective data into each cell without the key and punctuations. Each cell must display only the value extracted from the properties column. I've tried to go to Data>split text to ...
0
votes
1answer
79 views

Removing values in array without filter() in Google Sheets

I want to remove specific (or any) values in dynamic array/range and return the result of the resized array without those values without using filter() as I need to use arrayformula. For example, {&...
0
votes
1answer
83 views

Count unique tags

I’m trying to list and count all tags in my table. For some reason, anything after comma, is getting a white space before the word. So if the tag is found on the beginning of a record, it is seen as ...
1
vote
1answer
48 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
1answer
332 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 ...
0
votes
1answer
29 views

Import and SUM chosen (from drop-down) column to Sheet1 from Sheet2

What formula I can use in cell C2 to import one column from sheet 2 using a drop-down list? Here's the document.
0
votes
0answers
22 views

How to convert multiple form responses into easily readible table to use

I have a Google Form that is going to be used by different employees to add stock received. Since there is usually more than 1 item being received, I needed to provide space for them to add multiple ...
0
votes
2answers
379 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
3answers
74 views

=IMPORTHTML forces content to date format

I am a beginner with code and formulas here, so I was wondering if anyone could do exactly what was done on this question: Trying to use Google Sheets importHTML() to import a table. It forces content ...
2
votes
1answer
873 views

Unable to SUM column calculated by Array Formula

I've used Array Formula to calculate the total price for an order and I have a manual column next to it to note what has been paid. When I come to do a comparison of the two though, Google sheets ...
0
votes
1answer
2k views

Concatenating URL with a value to create link, but links only partially work

I have a column of cells in Google spreadsheet with values such as: b2397 b9504 b3333 b8655 bxxxx I would like to combine each value with a URL, for example: http://websitex.com?id= ...
0
votes
1answer
28 views

How to check separate sheet cell for any data, and return a Y?

I'm using several Google sheets to capture product data and optimize it. One sheet will feed data into the next, and then again into another sheet, using this formula: =IMPORTRANGE("https://url-to-...
1
vote
1answer
78 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?
0
votes
2answers
651 views

Dynamic Data Validation in Google Sheets

I want to restrict a value in a cell based on another cell in the same row. The value in A must not exceed what is in B. If the value in B is zero, any figure is allowed in A A | B --- 4 | 9 – ...
0
votes
1answer
77 views

SUM weekly values according to date in one column A and values in column B

As shown in the screenshot, I have values I am looking to add for weekly totals to track, however, this is very tedious having to create a new code for every week, especially as the weeks change per ...
3
votes
1answer
36 views

How to convert strings separated by comma from base 36 to decimal

I'm getting input from a google form. An associate scans a QR code giving a string like ^12t34:1 Cell B1 = ^12t34:1 The string between the ^ and : is base36. I can use C1 =DECIMAL(SUBSTITUTE(...
1
vote
1answer
23 views

Replacing items in comma delimited cell with same string each time

I'm trying to create a formula that will replace any contents of comma delimited cell with single string. The number of items in delimited cell will vary... Specifically: 524352435,2345235523,...
1
vote
3answers
1k 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,"="&...
0
votes
1answer
23 views

Create Columns via Formula for two dates

How do you do this with columns instead of rows? =ARRAYFORMULA(TEXT(ROW(INDIRECT("A"&DATEVALUE("2018-4-13")& ":A"&DATEVALUE(&...
1
vote
1answer
2k 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
12 views

Show image if rank equals number

I have a row of 5 cells. I want to show an image above each cell depending on the rank of the cell. So if the rank of the cell equals 1, show a specific image (from a link), if the rank is 2, show a ...
1
vote
2answers
56 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 ...
0
votes
1answer
25 views

Sort sentences based on formatting

I have a list of sentences in Google Sheets, each word in a different column. There is a stupid man. How we came here we don't know anything about. Finally, it has come to this. Shaking my head. How ...

1 2
3
4 5
12