Questions tagged [google-sheets-arrayformula]
Use for questions about the use of the Google Sheets built-in function ARRAYFORMULA.
726
questions
0
votes
1
answer
32
views
Creating a dynamic 2d array
I have to create variable length sequences from a column along its rows.
Eg- Suppose the column is as below
2
4
3
I want to create a sequence like this
2 | 1 | 2
4 | 1 | 2 | 3 | 4
3 | 1 | 2 | ...
- 1
0
votes
1
answer
29
views
How do I simplify this formula to be expandable?
The Example sheet
The formula:
For all x from 1 to 10, if (Sell <= Buy * x) then x, else ">=10"
I thought I might be able to cheat a little with the sequence function:
=ARRAYFORMULA(...
- 3
1
vote
0
answers
24
views
Filter date, and add blank rows between different time slots
I'm trying to build a reservations spreadsheet for my friend's restaurant where she can input on monthly sheets as resos come in, then see a filtered, sorted summary of any selected day.
Here is a ...
- 11
1
vote
1
answer
31
views
Text input if cell > than 1
I would only like the values in Column A to return if there is a value of >1 in Column B - I am unsure on a formula to do this
- 29
0
votes
1
answer
26
views
Match value of cell if in between range and text match
Desired outcome:
I would like to return a value in column C (column D is the values I'd like the formula to return) - the value in column C should return on the criteria it matches the product name in ...
- 29
1
vote
1
answer
39
views
Return matching text value in cell
I have a sheet of products and skus - I would like to have a formula that searches the entire column for matching text strings and returns it in a separate cell as the text string
Is this possible?
- 29
0
votes
1
answer
16
views
Use IFS in a SPARKLINE CHART
I'm at beginner level and trying to modify a template using a SPARKLINE chart. Now I would like the chart color to change depending on the cell value. I have done some research and thought IFS(CELL>...
- 1
0
votes
1
answer
21
views
"Multiply" (Concatenate) Text Columns in Google Sheets
Simple question that I can't seem to find a solution for. I have text data in two columns like
Name
Num
a
1
b
2
and I would like to produce a single formula if possible to output a 3rd column
...
- 1
0
votes
1
answer
19
views
How can i make a formula to determine payment types
I am having trouble nesting and using multiple criteria. I want a column to determine if payment type was CASH, CHECK or WAITING depending on if the cells for Check# and Payment are containing data.
...
0
votes
0
answers
7
views
Subtraction between SUM IF array
https://docs.google.com/spreadsheets/d/1GFS6J1vX88vsOCsKzrMB7Zf82m9QOHqu6oAmO4eJwH8/edit?fbclid=IwAR3Ek51w0QKx5t1bQ6T6YDy8FsKEfOHlh_UEee0MfuyPf0Fpl-ateVHVasQ#gid=0
In the above file, in column "G&...
0
votes
1
answer
18
views
I can not make a query work, I need another eyes to look it
I have 3 columns and what I need to do is a query where I can see
Lab name, Sum Job count only N, Sum redos only Y.
I tried with array, but not let me get the conditional Col2=N,
Please help me.
=...
- 1
0
votes
1
answer
16
views
[SUM IF CELL ABOVE EQUALS CELL BELOW]
[SUM IF CELL ABOVE EQUALS CELL BELOW].
*
link to the file https://docs.google.com/spreadsheets/d/1GFS6J1vX88vsOCsKzrMB7Zf82m9QOHqu6oAmO4eJwH8/edit#gid=0
*
in column f of the file I'd like to get the ...
0
votes
1
answer
17
views
What is wrong with the formular?
I am bloody new to this forum - so if I am doing something wrong, please let me know.
Here is my question: I want to build a URL in Google Sheets where you have to fill out some parts (it for tracking)...
- 1
0
votes
0
answers
10
views
Using a SUMIF(ARRAYFORMULA) to return from a column with IF(ISBLANK) Formula
I have a google sheet where there are two entries for each date, but I want them to count as 1 entry so I can average the total for a month.
I have a Column "Month/Year" pulled from the Date ...
0
votes
1
answer
24
views
In Google Sheets, how do I make an ARRAYFORMULA be the Boolean combination of two other columns?
I.e., if I have:
A
B
TRUE
TRUE
TRUE
FALSE
FALSE
TRUE
FALSE
FALSE
say I want to create a column C that is A OR B:
A
B
C
TRUE
TRUE
TRUE
TRUE
FALSE
TRUE
FALSE
TRUE
TRUE
FALSE
FALSE
FALSE
Is ...
- 111
4
votes
1
answer
130
views
Rearrange Name Format in Google Sheets using one formula only
Is there a simple way to do this using only one arrayed formula?
Here are the names listed in the above image:
NAME
dela Cruz, Antonio Karl Jr.
Woods, Oliver
Hasegawa, Ken Sr.
Sta. Maria, ...
- 45
0
votes
0
answers
17
views
Running a script on select tabs in Google Sheets [duplicate]
I am not a coder, but I can normally do enough trial and error to get my script to function in the way I want my google sheet to work.
The goal of my script:
I want cell A to time stamp anytime that ...
- 1
0
votes
3
answers
41
views
Need Help! Google Sheets Formula
Is there a simple formula for this?
Where it will look for a Name with "- Leader" in Column A and its corresponding Value in Column B, and it will return the Name of the Leader with the same ...
- 45
0
votes
1
answer
57
views
ArrayFormula COUNTUNIQUE
How would I turn this formula into an array formula so it automatically populated when a new row is created? =countunique(Z23:AC23)
I need the formula to count the unique properties in a row between ...
- 1
0
votes
2
answers
23
views
How to create Google Sheet app that retrieves a mix of random words
I have a Google Sheet with a lot of columns, each of them containing words from a category. I would love to create a formula that can retrieve random words and put them together.
For example:
Colum 1 [...
- 1
1
vote
2
answers
55
views
How Google Sheets ArrayFormula consider other column
In Row 1 of this sheet I have formulas like =ArrayFormula(sum(if(and(not(ISBLANK(C$4:C$49)),$A$4:$A$49>=$A$1),1,0))).
What am I doing wrong?
How can I change the formula such that the values in ...
- 582
0
votes
0
answers
16
views
Changing Future Monthly Sales Goals Based On Previous Monthly Sales
What I have is a spreadsheet which needs to calculate future monthly required sales goals, based on an annual sales goal, and previous monthly sales.
EXAMPLE: My annual sales goal is $1,200,000. This ...
0
votes
0
answers
12
views
I would like to sum values by month and by category [duplicate]
I have a personal finance expense tracker and want to be able to change the month with my data validation (Cell A9 in first screenshot), and sum by the categories below it. I'm stumped on this one and ...
- 1
0
votes
0
answers
6
views
Update a list based on data from corresponding cell
I've got a google form that updates a sheet for locker reservations, on the reservation sheet I have to manually add the locker number and assigned key status. I've got two columns on the main sheet '...
0
votes
0
answers
27
views
In Google Sheets, grab data from various cells (non touching) from 1 tab and bring to tab 2
I'm not 100% sure what specifically I'm asking here, I think I'm needing a variety of different formulas all in 1. In Google Sheets, I need to pull the results of checkboxes in tab 2, and bring them ...
0
votes
1
answer
74
views
USING INDEX() WITH ARRAYFORMULA
I have a list of data on A1:a7 (blue range) and i have a REF on D2:H2 (green range). I want to use the REF to search in the list where the row is equal to the REF.
The example result is on D3:H3
I put ...
- 195
0
votes
1
answer
17
views
How to combine cells of data in Googlesheet to a single/consolidate row and remove duplicates?
I'm trying to consolidate all the invoices that we get from the same contractor to a single row that will have the business name, all the memos/invoices for the week in one cell along with the added ...
0
votes
3
answers
32
views
Sum above cells matching crtiteria via ARRAYFORMULA
I have a sheet where data is added from time to time. The data is rather simple - object name and its quantity. I want to have a column with sum of object's quantity up to mentioned row. This is ...
0
votes
2
answers
70
views
Google Sheets: keep track of days spent in different countries
I have prepared a Google Sheet that lists all flights I have taken in recent years, alongside the departing and arriving country:
Date & time
From
To
Outbound country
Inbound country
Days in ...
0
votes
1
answer
18
views
How to create an array that will look up a string value in a sentence and return an int value in Google Sheets
My data is stored in Column F, the raw data looks like this:
The Images- had poor pixel quality.
What I want to do is to create an array formula that will look for my key word which is a string and ...
0
votes
1
answer
143
views
Repeat a whole row N times based on a cell value
I'm using Google Sheet and I am kind of new at it so I want to split test Column and repeating other columns in each row.
Before Splitting
FILE NUMBER || NUMBER OF TESTS || TEST || ...
0
votes
1
answer
18
views
Flexible formula to match strings in 1 row to run a formula in matching columns in another row
I have a row where all strings have the identical format (XX-#####-##) in which Xs are 2 upper case alphabet letters and the #s are integers.
Short example, see the doc for more
KC-30000-00 | KC-30000-...
- 21
0
votes
1
answer
146
views
Compare and remove duplicates in Arrays (Google sheets)
I have two arrays of many rows , in my Google sheets, which have numbers such as
Active
Recovered
904;1505;1706
406;504;1502;1503
202;401;602;1104;1501
202;1502;1602;1704
I have shown only two ...
- 199
0
votes
1
answer
23
views
Calculate specific contents of a cell that contains a string of numbers and text
I have a sheet that contains cells that have lists of data within them, for example:
One cell contains: 1xExample, 2xAnotherExample, 5xThirdExample
The cell below contains: 1xAnotherExample, ...
- 1
0
votes
0
answers
8
views
#REF error when changing Drive location
I have a Google Sheets template that locations are making a copy of. A small percentage make the copy successfully and are able to use then randomly the formula's return #REF. All formulas are ...
0
votes
1
answer
25
views
Trying to get array formula to work on Sheets
I am trying to to use the Array formula that includes a minus and divide value. Here is my formula:-
IF(J3:J="",,SUM(((B3:B+C3:C)+(E3:E+F3:F))-$X$3)/$X$3)
I tried inserting MINUS( before the ...
- 1
-2
votes
1
answer
34
views
i can't find the problem to this formula
=UNIQUE(QUERY({IMPORTRANGE("1xWx1FRfvCfmvYi9Sb7Gh5lIAb-NAt0RG0h-e_HKmxjU","APP Directory!A2:U");IMPORTRANGE("1xWx1FRfvCfmvYi9Sb7Gh5lIAb-NAt0RG0h-e_HKmxjU","APP ...
- 1
0
votes
0
answers
20
views
Advanced Array Formula Documentation / Multidimensional Arrays / Conditional Variable Ranges
I'm manually tracking games and wins every few days. Games goes up by 3-20 with each update, wins is about 50% of games. So data looks something like this:
Games Wins New Games New Wins
2800 ...
- 1
0
votes
1
answer
27
views
Google Sheets Combining Is Between and AND operator Conditional Formatting
my programming knowledge is not great and I need some help. I am trying to get conditional formatting to highlight any cell in a column that contains values between 13-16 AND in a different column (...
- 1
0
votes
2
answers
27
views
Is there a way to remove entries based on condition in =filter?
I probably did not phrase my question accurately. Is there a way to remove the result 'a' because it fulfils the '<>1' condition? I am pretty sure a mixture of query and arrayformula is needed ...
0
votes
1
answer
43
views
Reference "This Cell's Column" in Google Sheets
https://docs.google.com/spreadsheets/d/1LG7AgcumhfQ1ZV0Em_oVjdPdrLCP-5CWWrw0WV1oCnY/edit?usp=sharing
(We have to track 3 separate point totals for everyone who has participated in each game, game ...
0
votes
2
answers
44
views
How to show a report that has "numbers in thousands" as full numbers?
If I have a lot of numbers in thousands and I don't want to use regexp to append to them three zeros to see the real number how can I proceed? I didn't found yet a formatting solution or any other ...
1
vote
0
answers
8
views
Retrieve URLs from website tables [duplicate]
I'm trying to save table data from a website by using IMPORTXML and IMPORTHTML. These tables include text/numeric data, which I can retrieve with no problems, and hyperlinks wich I cannot retrieve. ...
- 11
0
votes
1
answer
96
views
Google Sheets - Arrayformula with textjoin, and, if
I'm trying to figure out how to make my arrayformula with textjoin to work, but the formula does not copy to another row.
I want the job no automatically generate when i write data in F3 cell & ...
- 3
0
votes
1
answer
49
views
Google Sheets IFS Text result based on multiple conditions FIX & SORT
I'm very new to Google Sheets and I'm trying to make find the correct formula for a text result based on multiple conditions, here is my best crack at it:
=arrayformula(IFS(B9:B="",V9:V=&...
0
votes
1
answer
9
views
How to assign a name to a membership number vice versa?
I would like to learn what formula I can use to make my timesheet more efficient.
How to assign a membership number to a name vice versa? I need to add lesson hours to each membership daily and ...
- 1
1
vote
1
answer
25
views
Google Sheets, Referring to a cell range
Here's what I am trying to do.
In cell C15, I put the cell reference of another cell, e.g. B2.
I another cell, cell C16, I want to check that IF the value in cell C15 contains the cell reference to ...
- 11
0
votes
0
answers
41
views
How to create a google sheets formula to return a value based on % values in other cells & weighted average formula? (FIXED)
I've been trying to create a scoring system for expected stock market returns -
Sheet
I'm having problems creating a formula to firstly give stocks a score in multiple conditions for example SS
I want ...
0
votes
1
answer
62
views
Arrayformula to get the max number of consecutive occurrence of a character inside a string
I have strings of different length like the one below:
LWLLLLXWLWWWXWWWXWWLWLWLWWWWLWLLLXXWLLLLL
From this string, I'd like to extract the maximum number of consecutive occurrence of L. Here it would ...
- 35
-1
votes
1
answer
37
views
Get a single row with true and false if the string is contained in a range
First of, sorry for the title, I have no clue what to search for or how to name the question.
I got an example sheet which might already explain everything:
https://docs.google.com/spreadsheets/d/...
- 7