Questions tagged [google-sheets-arrayformula]

Use for questions about the use of the Google Sheets built-in function ARRAYFORMULA.

156 questions with no upvoted or accepted answers
Filter by
Sorted by
Tagged with
0
votes
0answers
17 views

(Left) Joining multiple Tables for Dashboard with several shared Conditions/Filters

Goal: Interactive dashboard with data from different sheets but for the same campaigns. I got 3+ tables with social media Data in them. I want to combine them by unique ID. Additionally, the users ...
0
votes
0answers
16 views

Passing array from Google Form to script

I've currently a form that a user fills in and the data is then entered into a spreadsheet. All this works well. I now need to make one of the inputs an array so that more than one value can be ...
0
votes
0answers
14 views

Insert range using IF formula in SUMIF function with multiple criteria (using arrayformula)

I used an arrayformula with my sumif function which has several criteria and ranges to expand automatically across a set column (B). The formula below works perfectly: =ARRAYFORMULA(SUMIF('DATA'!$N:$...
0
votes
0answers
22 views

Dynamically retrieve entire row in Google Sheets for computation

Objective:I want to retrieve the content of the last row of a Google Form and do a simple computation with that and later return that value to the user. Example: The form contains two questions: "How ...
0
votes
0answers
25 views

Pull data for different sheet and keep formulas in new rows

I'm trying to accomplish 2 things at once. I have 2 pages with customer information, one page being contact info and another being CRM information based on those customers. I'm trying to populate ...
0
votes
0answers
21 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(...
0
votes
0answers
18 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
21 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 ...
0
votes
0answers
121 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
0answers
32 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
0answers
39 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,"...
0
votes
1answer
12 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 ...
0
votes
1answer
110 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
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
0answers
39 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 ...
0
votes
0answers
375 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
2answers
31 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
1answer
81 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 ...
0
votes
2answers
30 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
63 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
41 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 ...
0
votes
0answers
17 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 ...
0
votes
0answers
4 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
20 views

How can I extract specific information from the cells?

How can I extract just the: Brand, Showroom Only Product, etc.
0
votes
1answer
107 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
36 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
16 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 ...
0
votes
1answer
15 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
27 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
1answer
18 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/...
0
votes
1answer
25 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
46 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
34 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 ...
0
votes
1answer
24 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
1answer
20 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-...
0
votes
1answer
47 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 ...
0
votes
1answer
356 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
23 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 ...
0
votes
2answers
16 views

Using IF functions with formatting in Google Sheets

I'm working on a Google Sheet and want to reformat all instances where the number in a cell is "2" and the color of the cell is pink. Is it possible to use information about cell formatting (here, e.g....
0
votes
1answer
2k views

Repeat formula n number of times in Google Sheets

Apologies, I'm very new to Google Sheets and spreadsheets in general. I'm trying to fill some cells with repeating data, and then analyze it. The data is pulled from another large spreadsheet and has ...
0
votes
1answer
40 views

Fetch value of a cell based on dropdown menu items results on another sheet

I have 2 Sheets named as Sheet1 and Sheet2... Sheet1: Contains Drop down list on B4 Column. Based on the selection of an item, it pulls the result of the open price shown on D4 column. Sheet2: ...
0
votes
1answer
20 views

Assigning rank in another sheet based on Average

I have a list of names with an average beside it for every day Monday-Friday. A subgroup of names within this list will be highlighted. I want a macro to assign ranks of the highlighted individuals ...
0
votes
2answers
188 views

Sum of LAST Column in Google Sheets

I'm wondering if there's a way to sum the last Column in a sheet. I add on data every month and have to re-do multiple formulas every time I do, as they're always based on the sum of the last column. ...
0
votes
1answer
27 views

Having a formula output to a different cell

I have a Google Sheet in the following format: ID | VAL ------------- 1 | dataH 2 | dataW 3 | dataX 4 | dataC 5 | dataG 6 | dataL ... | ... ID and VAL are on cells A1 and B1, ...
0
votes
1answer
16 views

How do I search a string, and return all values?

How do I use the script editor to search a string in one cell, and return all results from another sheet? For Example: I have my string in cell B1 - which is the item number Data to be searched in a ...
0
votes
3answers
239 views

Checkbox to activate a column in QUERY

Is there a way to activate/deactivate a column in QUERY function? I have a query to query and selection of several columns Select Col1,Col31,Col32,Col33,Col25,Col26, and want to have a checkbox for ...
0
votes
1answer
31 views

What function formula should I use to calculate my salary so that I don't have to differentiate between Friday and weekend manually?

These are the formulae I am currently using where I have to manually sort out if its Friday or weekend by using conditional formatting to bring attention to the minority that needs a different formula....
0
votes
0answers
128 views

ARRAYFORMULA + QUERY + IMPORTRANGE - Final cell reference in IMPORTRANGE is acting like it's absolute when I need it to be relative

I know I'm chaining a lot together here, but it's SO close to functional. Here is my formula with the doc address altered for privacy. =ARRAYFORMULA(IF(ISBLANK($AE$3:$AE),(query(importrange("https://...
0
votes
2answers
29 views

Add columns based on rows

I'm currently working on setting up a sheet, but I want to add columns in one worksheet based on rows in another worksheet. I have no idea if this is even possible, I've tried googling it. Might be ...
0
votes
1answer
528 views

Increment number in array formula based on a condition

I have a data set with Sub-Total rows at various intervals. As the data can have rows inserted or deleted, I am trying to use an array formula to give each group of data an incremented integer as a ...