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
-1
votes
0answers
14 views

Auto fill a column with formulas when new rows are added

I need to write a formula which looks out for new rows entered into the spreadsheet and then auto-fills the my new cell in column with preset formula. i use it to get task competion date after ...
-1
votes
0answers
28 views

Doesn't return a value when turned into Arrayformula

I have a large sheet with around 4000 rows of data, and a config on another sheet. It all seems to work fine when the functions are in each cell, but once i tried turning it to an ArrayFormula, it ...
1
vote
1answer
41 views

Issue trying to sum different columns

I am trying to sum 3 different columns and I'm not being successful. My function is: =ARRAYFORMULA(SUMIF(MONTH(D:D), 2, A:C)) Where D is a column with dates, and A,B and C columns with currency (A-...
2
votes
0answers
93 views

Create a pivot table with a column for each assignee, containing rows of task descriptions

I have a two-column table with the first column being "task description" and second column being "assignee". This is something I'm unable to influence. Crude representation: I'd like to be able to ...
2
votes
1answer
13 views

SumIf OR COunt if

I want to count dates monthwise with person name. This is my data: Mandeep 1/2/2020 Mandeep 1/2/2020 Mandeep 1/2/2020 Mandeep 1/2/2020 Mandeep 1/2/2020 Mandeep 1/2/2020 Mandeep 2/2/2020 Mandeep 2/2/...
0
votes
0answers
20 views

How can I SUMIF a row, but have the formula automatically applied to all new rows below it in Google Sheets?

I've made an inventory tracking spreadsheet that gets raw data from Google Forms. Forms inputs data into Sheets in a less than ideal way, but there's nothing I can do about that, so I'm stuck post-...
3
votes
1answer
51 views

Print incremental numbers in Google Sheets

Cell A1 holds a numerical value (e.g. 10), and I'd like to output the list of numbers from 1 to that number in B1: What's the formula I need for B1? It seems trivial, and I know it probably requires ...
1
vote
2answers
31 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 ...
1
vote
1answer
40 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=...
0
votes
2answers
46 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
1answer
39 views

I need a direction for a dynamic array

I have a simple formula: ={ARRAYFORMULA(REGEXREPLACE( IF('B-Prename5'!$B$2:$F$7<>""; 'B-Prename5'!$A$1&", "; )& IF('C-Prename9'!$B$2:$F$7<>""; 'C-Prename9'!$A$1&", "; )&...
1
vote
1answer
23 views

How to use IF THAN statements on date but converting it to time

On B2, I'm trying to put in an IF timevalue statement on a datetimevalue statement with this formula =IF (A2 < TIME (7,30,0), "PREM", IF (A2 >= TIME (7,30,0), "INTRA", "0")) However it doesn'...
2
votes
2answers
47 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 ...
2
votes
1answer
41 views

Using dates as criterion for COUNTIFS in Google Sheets

So I'm currently making a recruiting data sheet for an online community. I'm trying to make it as automated as possible without actually making a bot to do it. How can I make a COUNTIF(or similar) ...
0
votes
0answers
42 views

Splitting checkbox response into multiple rows on another sheet

I am trying to take a Google Form response page, and change the way the data is displayed. Currently, I have the raw data on the first sheet, and I'm trying to format a second sheet that will split ...
1
vote
1answer
30 views

How do I find the sum of a specific row of values, but only for columns that contain a different specific value?

My sheet looks something like this: I'm trying to create a formula for the Total profit column, which would be the sum of the winnings/losses for that person for that row. So for instance, for ...
2
votes
1answer
69 views

Google Sheets - Using IMPORTRANGE with a variable number of rows

Google Sheets - Using IMPORTRANGE with a variable number of rows ...is it possible to add the required number of blank rows to avoid the IMPORTRANGE's overwrite error? Test Input: https://docs....
1
vote
0answers
15 views

Google Sheets Area Graph(s)

I need (want) to create a Google Sheets Area Graph (Chart). The Graph/Chart will represent "Date"s. I need the Xaxis to be the dates (as days) between these start and end dates: 01/27/2020, 4/25/2020 ...
2
votes
1answer
59 views

How to create no pop-up Hyperlinks/Bookmarks for an array of cells within the same tab/sheet

I'm trying to create a formula that will allow all the cells in Column B to include a hyperlink to cells in Column J in their corresponding rows. The kicker is that I'm trying to keep the current ...
0
votes
1answer
35 views

How to automatically apply a formula with relative reference to a whole column?

I have the following formula in every cell in my C column: =IF(ISBLANK(B4),"",CONCATENATE("https://www.url.com/",B4)) If the cell in the B column has content, it takes that content and concatenates ...
0
votes
2answers
27 views

Does arrayformula help inside a Google sheets query?

Im making a query that includes a bunch of importranges and each search takes a while. The importranges are put together using { array signs }. If I were to use an arrayformula to make an array of ...
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
1answer
32 views

More compact version of nested substitute function possible?

I want to make about a hundred changes in a column on a spreadsheet. These changes have to be remade each time a new copy of the original comes. The changes are spread over several thousand rows. ...
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
1answer
17 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
69 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 ...
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 ...
1
vote
1answer
36 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
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
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(...
1
vote
1answer
29 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
33 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 ...
1
vote
1answer
27 views

If “text” then, how to put a conditional formula

There are three columns. A, B and results. I need to make a formula that gives the following: If the text in both columns A and B is WIN, then in the results we should see: Winner. If the text in ...
1
vote
2answers
396 views

“Error Did not find value '____' in VLOOKUP evaluation.”

Please see my public spreadsheet here: https://docs.google.com/spreadsheets/d/11RMN7uN_woEQVzlDEkX6vSARTCGYYBjWs11ZDU-6Shc/edit#gid=1276951687 My goal is to get the Location Code from tab 1 into tab ...
2
votes
1answer
52 views

COUNTIF with Multiplicities

I am currently creating a histogram on Google Sheets to count how many of x-product in our inventory we have priced at less than $10k, between $10k to $20k, $20k to $30k, etc. The problem is that ...
1
vote
1answer
23 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
42 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
26 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
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 ...
-1
votes
1answer
32 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
89 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
63 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
41 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
27 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
122 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
23 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
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 ...

1
2 3 4 5
11