Questions tagged [google-sheets-arrayformula]
Use for questions about the use of the Google Sheets built-in function ARRAYFORMULA.
808
questions
1
vote
1
answer
40
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
- 87
0
votes
1
answer
120
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 ...
- 87
1
vote
1
answer
58
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?
- 87
1
vote
1
answer
118
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>...
- 11
0
votes
1
answer
46
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
21
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
12
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&...
1
vote
1
answer
24
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.
=...
- 11
-1
votes
1
answer
25
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
23
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
15
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
133
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
170
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, ...
- 55
0
votes
0
answers
21
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
67
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 ...
- 55
1
vote
1
answer
121
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 ...
- 11
0
votes
2
answers
36
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
63
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 ...
- 672
0
votes
1
answer
2k
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 ...
- 255
0
votes
1
answer
20
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
36
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
3
answers
168
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 ...
1
vote
1
answer
120
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 ...
1
vote
1
answer
702
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 || ...
- 11
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-...
- 65
0
votes
1
answer
242
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
40
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
1
vote
1
answer
41
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 ...
- 9
-2
votes
1
answer
48
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
1
answer
72
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
36
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
75
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
89
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 ...
- 103
1
vote
0
answers
9
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
545
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
78
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
70
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
32
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
47
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
169
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
0
votes
1
answer
126
views
Splitting a list contained within a single cell and want to associate/add text from a different cell in the same row to the new separated list
I am using Google Forms to collect information. In google sheet, I have rows of data and one of the cells contains lists that needs to be separated out.
=query(FLATTEN(ArrayFormula(IFERROR(SPLIT(G2:G,...
- 3
0
votes
2
answers
30
views
Google Sheets: Make the cell data the name of the link
Update:
I have attached a pic for example.
--------
I have ID numbers in each cell and I want that number to be a link to their file.
I know how to make a separate hyperlink that will ref that cell to ...
- 1
0
votes
1
answer
25
views
Google Sheet how to combine data from same sheet to other sheet
I have several tables on the same sheet with different numbers of rows and columns but basically have the same structure, now I want data from these different tables to be united in one master ...
- 3
1
vote
1
answer
32
views
Need To Randomize Selections From A List But Ensure Uniqueness
I'm building a tool to randomize setup for a very large modular card game. There are several lists from which I need to select 2 to 6 non-duplicate entries, and each list is on a separate tab from the ...
- 11
1
vote
1
answer
54
views
Arrayformula to get closest non-numeric value above
I have Column A with mixed data types (numbers and strings) and in Column B I want for each numbers in Column A to display the string that is the closest above. Example:
Title|
1|Title
2|Title
Next ...
- 35
1
vote
1
answer
351
views
How to copy a cell from Google Sheets without getting double quotes (" becoming ""), BUT keeping the \n (char(10)) line breaks
I have a couple of Google Spreadsheets I use to generate config files for things. The config file data ends up in a single cell, which is copied and pasted.
Problem is Google Sheets has this odd ...
- 131
0
votes
1
answer
127
views
Google sheets reference Text in Cell in Array Formula & Countif formula
So, I have a Array Formula and Countif formula in place because I need to know the total of entries in a combination of ranges. I need to know the sum of the number of cells that have "X" ...
0
votes
0
answers
54
views
Array Formula Problem - How to Fill In Blank Rows with the Same Value before a Breakpoint which advances to the next letter
I have a Google Sheet which is grouped into segments of a TV show. So there's an A segment and then a B segment and so on ... I have allocated two columns to provide two different timing functions (...
- 5
1
vote
0
answers
116
views
Use ArrayFormula to automatically update the column with a formula already containing an ArrayFormula
I am using the below formula to concatenate some data into one string:
=TEXTJOIN("",FALSE,ARRAYFORMULA(IF((Data!$F$3:$F=$C2)*(Data!$G$3:$G=$D2),Data!$L$3:$L,"")))
I however have ...
- 35