Questions tagged [google-sheets-arrayformula]
Use for questions about the use of the Google Sheets built-in function ARRAYFORMULA.
512
questions
-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 ...
